tag:blogger.com,1999:blog-46673829078461822842024-03-19T10:05:27.395+02:00YA(H)Q BlogYet another (hopefully helpful) Qlikview BlogJonathanhttp://www.blogger.com/profile/01169660018216478519noreply@blogger.comBlogger23125tag:blogger.com,1999:blog-4667382907846182284.post-38712959591643910472016-02-21T19:38:00.002+02:002016-02-21T19:38:59.232+02:00QVD Metadata<h2>
Examining QVD metadata</h2>
Qlikview data files (extension .qvd, referred to as QVD files in this article) are used for intermediate data storage, history, incremental loads and so on. The file contains the full information of a single data table and the files are usually generated and consumed by the load script. The metadata is useful when developing and operating multitier Qlikview setups.<br />
<br />
What you may not know is that the qvd file contains extensive metadata for the table. This includes:<br />
<ul>
<li>The name of the qvw file that created the QVD file</li>
<li>The version of Qlikview when the load script that created the QVD was run</li>
<li>The time the file was created</li>
<li>Metadata for the fields (name, number of unique values, data type, width, symbol table offset etc</li>
<li>The number of rows in the table</li>
<li>Lineage data, including data sources, SQL queries and connection data</li>
</ul>
This metadata is found in an XML header in the first part of the file. There are several ways of accessing this metadata without loading the entire file <br />
<br />
<h2>
Windows Batch "More" </h2>
A simple way to look at the metadata (as raw XML) is to use the Windows batch "more" command running in a console window. To set his up, you need a simple batch file. These are the steps to set this up:<br />
<ul>
<li>Create a text file on your desktop using a text editor (notepad will do, although I prefer Notepad++)</li>
<li>Add a single line: more "%1"</li>
<li>Save the file.</li>
</ul>
If you saved the batch file on your desktop, you can drag a QVD file and drop it on the file icon. A command or console window will open showing the first part of the XML metadata header. Press space to get the next page full. One you get a whole bunch of strange characters, you have reahed the end of the header and are looking at the symbol table which follows the XML header.<br />
<br />
This approach is simple, but not always the most convenient way to get the metadata you need.<br />
<br />
<h2>
Powershell Script</h2>
To simplify extracting the metadata and displaying it in a clear layout, I have created a Powershell script to extract the XML and unpack the XML elements. Powershell is a standard component of Windows since Windows XP/Server 2003.<br />
<br />
If you are not already using Powershell, create a scripts folder: eg C:\Scripts or C:\Users\Fred\Scripts. Download the script ps1 file and copy to the scripts folder you created.<br />
<br />
<a href="https://drive.google.com/open?id=0BzFdSk9UQtT8NFdveUlFUGhMdVk">qvdMeta.ps1</a><br />
<br />
You can use this script using the following command line (type in CMD window):<br />
><span style="font-family: "Courier New",Courier,monospace;">powershell C:\Scripts\qvdMeta.ps1 -source <i><qvdfile path=""></qvdfile></i></span><br />
<br />
You can add this to a batch file saved on your desktop:<br />
<i> </i><br />
<span style="font-family: "Courier New",Courier,monospace;">@echo off</span><br />
<span style="font-family: "Courier New",Courier,monospace;">powershell C:\Scripts\qvdMeta.ps1 -source %1</span><br />
<br />
<span style="font-family: inherit;">Now drag the qvd file and drop it on this file to see the fie metadata:</span><br />
<br />
<span style="font-size: x-small;"><span style="font-family: "Courier New",Courier,monospace;">QVD Meta data extractor V1<br /><br />Source file C:\Data\QVDs\Balances.qvd<br />Created by C:\QVDeploy\QVDCreators\Balances.qvw<br />Qlikview build 12664<br /><br />No of Rows 231 372<br />Table name BALANCES<br />Build time 2015-12-08 01:26:59<br /><br />Fields:<br /> EffDate 1 282 values<br /> RelNo 389 values<br /> Company 8 values<br /> FundCode 29 values<br /> ClientName 362 values<br /> ClientBalance 9 855 values<br /><br />XML lines 201 ; bytes = 6 499:</span></span><span style="font-size: x-small;"><span style="font-family: "Courier New",Courier,monospace;"></span></span><br />
<span style="font-size: x-small;"><span style="font-family: "Courier New",Courier,monospace;"></span></span><br />
<br />
<i>Note the script has been tested with QVD file produced by QV 11.20 up to SR13. I have not tested it with QVD files produced by Qlik Sense, or QV 12. It may need minor mods.</i><br />
<h2>
Open using Qlikview</h2>
<span style="font-family: inherit;">If you are not interested in the metadata, but want to exam<span style="font-family: inherit;">ine a qvd, y</span>ou can <span style="font-family: inherit;">read the qvd in Qlikview - just drag the qvd and <span style="font-family: inherit;">drop on the QV icon on your desktop. This will create a<span style="font-family: inherit;"> load script. Now click <span style="font-family: inherit;">File | Reload, and create some list b<span style="font-family: inherit;">oxes<span style="font-family: inherit;">, <span style="font-family: inherit;">and a table<span style="font-family: inherit;"> b<span style="font-family: inherit;">o</span>x for a quick vie<span style="font-family: inherit;">w of the content.</span></span></span></span></span></span></span></span></span></span><br />
<br />
<span style="font-family: inherit;"><span style="font-family: inherit;"><span style="font-family: inherit;"><span style="font-family: inherit;"><span style="font-family: inherit;"><span style="font-family: inherit;"><span style="font-family: inherit;"><span style="font-family: inherit;"><span style="font-family: inherit;"><span style="font-family: inherit;">To view the metadata only, ch<span style="font-family: inherit;">ange the file type in the QV file preview to XML. Now you can load the XML into QV rather than the actual data.</span> </span></span></span></span></span></span></span></span></span></span><br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
Jonathanhttp://www.blogger.com/profile/01169660018216478519noreply@blogger.com4tag:blogger.com,1999:blog-4667382907846182284.post-58444520206993569972014-07-13T23:17:00.000+02:002014-07-13T23:17:41.343+02:00Time and Date Confusion 2<!--[if gte mso 9]><xml>
<w:WordDocument>
<w:View>Normal</w:View>
<w:Zoom>0</w:Zoom>
<w:TrackMoves/>
<w:TrackFormatting/>
<w:PunctuationKerning/>
<w:ValidateAgainstSchemas/>
<w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid>
<w:IgnoreMixedContent>false</w:IgnoreMixedContent>
<w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText>
<w:DoNotPromoteQF/>
<w:LidThemeOther>EN-ZA</w:LidThemeOther>
<w:LidThemeAsian>X-NONE</w:LidThemeAsian>
<w:LidThemeComplexScript>X-NONE</w:LidThemeComplexScript>
<w:Compatibility>
<w:BreakWrappedTables/>
<w:SnapToGridInCell/>
<w:WrapTextWithPunct/>
<w:UseAsianBreakRules/>
<w:DontGrowAutofit/>
<w:SplitPgBreakAndParaMark/>
<w:DontVertAlignCellWithSp/>
<w:DontBreakConstrainedForcedTables/>
<w:DontVertAlignInTxbx/>
<w:Word11KerningPairs/>
<w:CachedColBalance/>
</w:Compatibility>
<m:mathPr>
<m:mathFont m:val="Cambria Math"/>
<m:brkBin m:val="before"/>
<m:brkBinSub m:val="--"/>
<m:smallFrac m:val="off"/>
<m:dispDef/>
<m:lMargin m:val="0"/>
<m:rMargin m:val="0"/>
<m:defJc m:val="centerGroup"/>
<m:wrapIndent m:val="1440"/>
<m:intLim m:val="subSup"/>
<m:naryLim m:val="undOvr"/>
</m:mathPr></w:WordDocument>
</xml><![endif]--><br />
<!--[if gte mso 9]><xml>
<w:LatentStyles DefLockedState="false" DefUnhideWhenUsed="true"
DefSemiHidden="true" DefQFormat="false" DefPriority="99"
LatentStyleCount="267">
<w:LsdException Locked="false" Priority="0" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Normal"/>
<w:LsdException Locked="false" Priority="9" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="heading 1"/>
<w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 2"/>
<w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 3"/>
<w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 4"/>
<w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 5"/>
<w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 6"/>
<w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 7"/>
<w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 8"/>
<w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 9"/>
<w:LsdException Locked="false" Priority="39" Name="toc 1"/>
<w:LsdException Locked="false" Priority="39" Name="toc 2"/>
<w:LsdException Locked="false" Priority="39" Name="toc 3"/>
<w:LsdException Locked="false" Priority="39" Name="toc 4"/>
<w:LsdException Locked="false" Priority="39" Name="toc 5"/>
<w:LsdException Locked="false" Priority="39" Name="toc 6"/>
<w:LsdException Locked="false" Priority="39" Name="toc 7"/>
<w:LsdException Locked="false" Priority="39" Name="toc 8"/>
<w:LsdException Locked="false" Priority="39" Name="toc 9"/>
<w:LsdException Locked="false" Priority="35" QFormat="true" Name="caption"/>
<w:LsdException Locked="false" Priority="10" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Title"/>
<w:LsdException Locked="false" Priority="1" Name="Default Paragraph Font"/>
<w:LsdException Locked="false" Priority="11" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Subtitle"/>
<w:LsdException Locked="false" Priority="22" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Strong"/>
<w:LsdException Locked="false" Priority="20" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Emphasis"/>
<w:LsdException Locked="false" Priority="59" SemiHidden="false"
UnhideWhenUsed="false" Name="Table Grid"/>
<w:LsdException Locked="false" UnhideWhenUsed="false" Name="Placeholder Text"/>
<w:LsdException Locked="false" Priority="1" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="No Spacing"/>
<w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading"/>
<w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List"/>
<w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid"/>
<w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1"/>
<w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2"/>
<w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1"/>
<w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2"/>
<w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1"/>
<w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2"/>
<w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3"/>
<w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List"/>
<w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading"/>
<w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List"/>
<w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid"/>
<w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading Accent 1"/>
<w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List Accent 1"/>
<w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid Accent 1"/>
<w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1 Accent 1"/>
<w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2 Accent 1"/>
<w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1 Accent 1"/>
<w:LsdException Locked="false" UnhideWhenUsed="false" Name="Revision"/>
<w:LsdException Locked="false" Priority="34" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="List Paragraph"/>
<w:LsdException Locked="false" Priority="29" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Quote"/>
<w:LsdException Locked="false" Priority="30" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Intense Quote"/>
<w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2 Accent 1"/>
<w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1 Accent 1"/>
<w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2 Accent 1"/>
<w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3 Accent 1"/>
<w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List Accent 1"/>
<w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading Accent 1"/>
<w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List Accent 1"/>
<w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid Accent 1"/>
<w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading Accent 2"/>
<w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List Accent 2"/>
<w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid Accent 2"/>
<w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1 Accent 2"/>
<w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2 Accent 2"/>
<w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1 Accent 2"/>
<w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2 Accent 2"/>
<w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1 Accent 2"/>
<w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2 Accent 2"/>
<w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3 Accent 2"/>
<w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List Accent 2"/>
<w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading Accent 2"/>
<w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List Accent 2"/>
<w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid Accent 2"/>
<w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading Accent 3"/>
<w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List Accent 3"/>
<w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid Accent 3"/>
<w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1 Accent 3"/>
<w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2 Accent 3"/>
<w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1 Accent 3"/>
<w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2 Accent 3"/>
<w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1 Accent 3"/>
<w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2 Accent 3"/>
<w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3 Accent 3"/>
<w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List Accent 3"/>
<w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading Accent 3"/>
<w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List Accent 3"/>
<w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid Accent 3"/>
<w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading Accent 4"/>
<w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List Accent 4"/>
<w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid Accent 4"/>
<w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1 Accent 4"/>
<w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2 Accent 4"/>
<w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1 Accent 4"/>
<w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2 Accent 4"/>
<w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1 Accent 4"/>
<w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2 Accent 4"/>
<w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3 Accent 4"/>
<w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List Accent 4"/>
<w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading Accent 4"/>
<w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List Accent 4"/>
<w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid Accent 4"/>
<w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading Accent 5"/>
<w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List Accent 5"/>
<w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid Accent 5"/>
<w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1 Accent 5"/>
<w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2 Accent 5"/>
<w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1 Accent 5"/>
<w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2 Accent 5"/>
<w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1 Accent 5"/>
<w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2 Accent 5"/>
<w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3 Accent 5"/>
<w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List Accent 5"/>
<w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading Accent 5"/>
<w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List Accent 5"/>
<w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid Accent 5"/>
<w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading Accent 6"/>
<w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List Accent 6"/>
<w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid Accent 6"/>
<w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1 Accent 6"/>
<w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2 Accent 6"/>
<w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1 Accent 6"/>
<w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2 Accent 6"/>
<w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1 Accent 6"/>
<w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2 Accent 6"/>
<w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3 Accent 6"/>
<w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List Accent 6"/>
<w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading Accent 6"/>
<w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List Accent 6"/>
<w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid Accent 6"/>
<w:LsdException Locked="false" Priority="19" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Subtle Emphasis"/>
<w:LsdException Locked="false" Priority="21" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Intense Emphasis"/>
<w:LsdException Locked="false" Priority="31" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Subtle Reference"/>
<w:LsdException Locked="false" Priority="32" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Intense Reference"/>
<w:LsdException Locked="false" Priority="33" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Book Title"/>
<w:LsdException Locked="false" Priority="37" Name="Bibliography"/>
<w:LsdException Locked="false" Priority="39" QFormat="true" Name="TOC Heading"/>
</w:LatentStyles>
</xml><![endif]--><!--[if gte mso 10]>
<style>
/* Style Definitions */
table.MsoNormalTable
{mso-style-name:"Table Normal";
mso-tstyle-rowband-size:0;
mso-tstyle-colband-size:0;
mso-style-noshow:yes;
mso-style-priority:99;
mso-style-qformat:yes;
mso-style-parent:"";
mso-padding-alt:0cm 5.4pt 0cm 5.4pt;
mso-para-margin-top:0cm;
mso-para-margin-right:0cm;
mso-para-margin-bottom:10.0pt;
mso-para-margin-left:0cm;
line-height:115%;
mso-pagination:widow-orphan;
font-size:11.0pt;
font-family:"Calibri","sans-serif";
mso-ascii-font-family:Calibri;
mso-ascii-theme-font:minor-latin;
mso-hansi-font-family:Calibri;
mso-hansi-theme-font:minor-latin;
mso-fareast-language:EN-US;}
</style>
<![endif]-->
<br />
<h2 class="MsoNormal">
Date conversion or interpretation functions</h2>
<div class="MsoNormal">
Qlikview is able to automatically interpret dates that match
te format set in your PC locale, or in the the Qlikview environment variables
(usually automatically added to the start of the script in a new document)</div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal" style="margin-left: 36.0pt;">
Set TimeFormat='hh:mm:ss';<br />
Set DateFormat='YYYY/MM/DD';<br />
Set TimestampFormat='YYYY/MM/DD hh:mm:ss[.fff]';</div>
<div class="MsoNormal" style="margin-left: 36.0pt;">
<br /></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0cm; mso-layout-grid-align: none; text-autospace: none;">
However, the date and
time values in your source systems are frequently in a different format. This
is where the date interpretation functions come in:</div>
<br />
<ul>
<li>Date#(stringvalue[, format])</li>
<li>Time#(stringvalue[, format])</li>
<li>Timestamp#(stringvalue[, format])</li>
<li><span style="font-family: Symbol; mso-bidi-font-family: Symbol; mso-fareast-font-family: Symbol;"><span style="mso-list: Ignore;"><span style="font: 7.0pt "Times New Roman";"></span></span></span>Interval#(stringvalue[, format]) </li>
</ul>
<br />
<div class="MsoListParagraphCxSpLast" style="mso-list: l0 level1 lfo1; text-indent: -18.0pt;">
</div>
The functions ensure that the date field is correctly
interpreted. So lets assume that the field is in YYYYMMDD format (eg in a load
statement):<br />
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="mso-tab-count: 1;"> </span>Date#(SourceField,
‘YYYYMMDD’) As DateField,</div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
The interpretation functions only affect the numeric value
of the dual. The above expression will return the correct numeric value, but
the text format will still be YYYYMMDD format, as this what Qlikview inferred
as the correct format from the first record. If you would like to corrrectly
interpret the date and format it on the default format for you
environment/locale, them combine the interpretation function with the relevamt
formatting function, like this:</div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="mso-tab-count: 1;"> </span>Date(Date#(‘YYYYMMDD’))
As DateField,<br />
<span style="mso-spacerun: yes;"></span>
<span style="mso-tab-count: 1;"> </span>Date(Date#(‘YYYYMMDD’), ‘D
MMM YYYY’) As DateField2,<span style="mso-spacerun: yes;"> </span> </div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
The first will produce a date formatted in the default date format and the second in D MMM YYYY format.</div>
<div class="MsoNormal">
</div>
<div class="MsoNormal">
Hopefully this post and the previous one helps you to understand how to use the date/time format and
interpretation functions.</div>
Jonathanhttp://www.blogger.com/profile/01169660018216478519noreply@blogger.com4tag:blogger.com,1999:blog-4667382907846182284.post-69412671118121275592014-07-13T23:10:00.001+02:002014-07-13T23:10:09.934+02:00Time and Date Confusion 1<h3>
Part 1 Formatting Functions</h3>
<!--[if gte mso 9]><xml>
<w:WordDocument>
<w:View>Normal</w:View>
<w:Zoom>0</w:Zoom>
<w:TrackMoves/>
<w:TrackFormatting/>
<w:PunctuationKerning/>
<w:ValidateAgainstSchemas/>
<w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid>
<w:IgnoreMixedContent>false</w:IgnoreMixedContent>
<w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText>
<w:DoNotPromoteQF/>
<w:LidThemeOther>EN-ZA</w:LidThemeOther>
<w:LidThemeAsian>X-NONE</w:LidThemeAsian>
<w:LidThemeComplexScript>X-NONE</w:LidThemeComplexScript>
<w:Compatibility>
<w:BreakWrappedTables/>
<w:SnapToGridInCell/>
<w:WrapTextWithPunct/>
<w:UseAsianBreakRules/>
<w:DontGrowAutofit/>
<w:SplitPgBreakAndParaMark/>
<w:DontVertAlignCellWithSp/>
<w:DontBreakConstrainedForcedTables/>
<w:DontVertAlignInTxbx/>
<w:Word11KerningPairs/>
<w:CachedColBalance/>
</w:Compatibility>
<m:mathPr>
<m:mathFont m:val="Cambria Math"/>
<m:brkBin m:val="before"/>
<m:brkBinSub m:val="--"/>
<m:smallFrac m:val="off"/>
<m:dispDef/>
<m:lMargin m:val="0"/>
<m:rMargin m:val="0"/>
<m:defJc m:val="centerGroup"/>
<m:wrapIndent m:val="1440"/>
<m:intLim m:val="subSup"/>
<m:naryLim m:val="undOvr"/>
</m:mathPr></w:WordDocument>
</xml><![endif]--><br />
<!--[if gte mso 9]><xml>
<w:LatentStyles DefLockedState="false" DefUnhideWhenUsed="true"
DefSemiHidden="true" DefQFormat="false" DefPriority="99"
LatentStyleCount="267">
<w:LsdException Locked="false" Priority="0" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Normal"/>
<w:LsdException Locked="false" Priority="9" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="heading 1"/>
<w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 2"/>
<w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 3"/>
<w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 4"/>
<w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 5"/>
<w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 6"/>
<w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 7"/>
<w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 8"/>
<w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 9"/>
<w:LsdException Locked="false" Priority="39" Name="toc 1"/>
<w:LsdException Locked="false" Priority="39" Name="toc 2"/>
<w:LsdException Locked="false" Priority="39" Name="toc 3"/>
<w:LsdException Locked="false" Priority="39" Name="toc 4"/>
<w:LsdException Locked="false" Priority="39" Name="toc 5"/>
<w:LsdException Locked="false" Priority="39" Name="toc 6"/>
<w:LsdException Locked="false" Priority="39" Name="toc 7"/>
<w:LsdException Locked="false" Priority="39" Name="toc 8"/>
<w:LsdException Locked="false" Priority="39" Name="toc 9"/>
<w:LsdException Locked="false" Priority="35" QFormat="true" Name="caption"/>
<w:LsdException Locked="false" Priority="10" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Title"/>
<w:LsdException Locked="false" Priority="1" Name="Default Paragraph Font"/>
<w:LsdException Locked="false" Priority="11" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Subtitle"/>
<w:LsdException Locked="false" Priority="22" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Strong"/>
<w:LsdException Locked="false" Priority="20" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Emphasis"/>
<w:LsdException Locked="false" Priority="59" SemiHidden="false"
UnhideWhenUsed="false" Name="Table Grid"/>
<w:LsdException Locked="false" UnhideWhenUsed="false" Name="Placeholder Text"/>
<w:LsdException Locked="false" Priority="1" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="No Spacing"/>
<w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading"/>
<w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List"/>
<w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid"/>
<w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1"/>
<w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2"/>
<w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1"/>
<w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2"/>
<w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1"/>
<w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2"/>
<w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3"/>
<w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List"/>
<w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading"/>
<w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List"/>
<w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid"/>
<w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading Accent 1"/>
<w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List Accent 1"/>
<w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid Accent 1"/>
<w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1 Accent 1"/>
<w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2 Accent 1"/>
<w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1 Accent 1"/>
<w:LsdException Locked="false" UnhideWhenUsed="false" Name="Revision"/>
<w:LsdException Locked="false" Priority="34" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="List Paragraph"/>
<w:LsdException Locked="false" Priority="29" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Quote"/>
<w:LsdException Locked="false" Priority="30" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Intense Quote"/>
<w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2 Accent 1"/>
<w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1 Accent 1"/>
<w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2 Accent 1"/>
<w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3 Accent 1"/>
<w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List Accent 1"/>
<w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading Accent 1"/>
<w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List Accent 1"/>
<w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid Accent 1"/>
<w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading Accent 2"/>
<w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List Accent 2"/>
<w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid Accent 2"/>
<w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1 Accent 2"/>
<w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2 Accent 2"/>
<w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1 Accent 2"/>
<w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2 Accent 2"/>
<w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1 Accent 2"/>
<w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2 Accent 2"/>
<w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3 Accent 2"/>
<w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List Accent 2"/>
<w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading Accent 2"/>
<w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List Accent 2"/>
<w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid Accent 2"/>
<w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading Accent 3"/>
<w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List Accent 3"/>
<w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid Accent 3"/>
<w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1 Accent 3"/>
<w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2 Accent 3"/>
<w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1 Accent 3"/>
<w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2 Accent 3"/>
<w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1 Accent 3"/>
<w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2 Accent 3"/>
<w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3 Accent 3"/>
<w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List Accent 3"/>
<w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading Accent 3"/>
<w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List Accent 3"/>
<w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid Accent 3"/>
<w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading Accent 4"/>
<w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List Accent 4"/>
<w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid Accent 4"/>
<w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1 Accent 4"/>
<w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2 Accent 4"/>
<w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1 Accent 4"/>
<w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2 Accent 4"/>
<w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1 Accent 4"/>
<w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2 Accent 4"/>
<w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3 Accent 4"/>
<w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List Accent 4"/>
<w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading Accent 4"/>
<w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List Accent 4"/>
<w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid Accent 4"/>
<w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading Accent 5"/>
<w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List Accent 5"/>
<w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid Accent 5"/>
<w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1 Accent 5"/>
<w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2 Accent 5"/>
<w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1 Accent 5"/>
<w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2 Accent 5"/>
<w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1 Accent 5"/>
<w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2 Accent 5"/>
<w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3 Accent 5"/>
<w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List Accent 5"/>
<w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading Accent 5"/>
<w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List Accent 5"/>
<w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid Accent 5"/>
<w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading Accent 6"/>
<w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List Accent 6"/>
<w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid Accent 6"/>
<w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1 Accent 6"/>
<w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2 Accent 6"/>
<w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1 Accent 6"/>
<w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2 Accent 6"/>
<w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1 Accent 6"/>
<w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2 Accent 6"/>
<w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3 Accent 6"/>
<w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List Accent 6"/>
<w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading Accent 6"/>
<w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List Accent 6"/>
<w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid Accent 6"/>
<w:LsdException Locked="false" Priority="19" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Subtle Emphasis"/>
<w:LsdException Locked="false" Priority="21" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Intense Emphasis"/>
<w:LsdException Locked="false" Priority="31" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Subtle Reference"/>
<w:LsdException Locked="false" Priority="32" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Intense Reference"/>
<w:LsdException Locked="false" Priority="33" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Book Title"/>
<w:LsdException Locked="false" Priority="37" Name="Bibliography"/>
<w:LsdException Locked="false" Priority="39" QFormat="true" Name="TOC Heading"/>
</w:LatentStyles>
</xml><![endif]--><!--[if gte mso 10]>
<style>
/* Style Definitions */
table.MsoNormalTable
{mso-style-name:"Table Normal";
mso-tstyle-rowband-size:0;
mso-tstyle-colband-size:0;
mso-style-noshow:yes;
mso-style-priority:99;
mso-style-qformat:yes;
mso-style-parent:"";
mso-padding-alt:0cm 5.4pt 0cm 5.4pt;
mso-para-margin-top:0cm;
mso-para-margin-right:0cm;
mso-para-margin-bottom:10.0pt;
mso-para-margin-left:0cm;
line-height:115%;
mso-pagination:widow-orphan;
font-size:11.0pt;
font-family:"Calibri","sans-serif";
mso-ascii-font-family:Calibri;
mso-ascii-theme-font:minor-latin;
mso-hansi-font-family:Calibri;
mso-hansi-theme-font:minor-latin;
mso-fareast-language:EN-US;}
</style>
<![endif]-->
<br />
<div class="MsoNormal">
A common cause of issues on the QlikCommunity forum is date
fields and the use of the formatting and interpretation functions.</div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
First of all, let me state that there is no specific date data type
in Qlikview. There are only two data types in Qlikview – text (or character)
and number (or numeric). All number values are actually ‘duals’ – a dual has a
text representation and an underlying numeric value. A date is simply a dual
value with formatted text representation and an underlying value described
below.</div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
The integer portion of a date is the number of days since
midnight of December 31 1899. So 1 January 1900 is day 1. July 7 2014 is day
number 41827. This is identical to Excel and SQL Server as well as many other
products.</div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
The fractional portion of a date/time value represents the
proportion of 1 day. So 41827.0 is time 00:00 on 7 July. 41827.25 is 6:00am
(0.25 * 24). A pure date has a fractional part of 0. A pure time has only a
fractional part (0.000000 – 0.9999999). </div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
An interval has both parts where the integer part represents
days and the fractional part represents portions of a day, So 0.75 is an
interval of 18 hours (0.75 * 24) or 1 080 minutes (0.75 * 24 * 60) and 1.5 is
an intrerval of 25 hours.</div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
One important point is that the value of the date dual is
the underlying numeric, regardless of the chosen text representation.</div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
So lets move on to the formatting functions:</div>
<br />
<ul>
<li><span style="font-family: Symbol; mso-bidi-font-family: Symbol; mso-fareast-font-family: Symbol;"><span style="mso-list: Ignore;"><span style="font: 7.0pt "Times New Roman";"></span></span></span>Date(numericvalue[, format])</li>
<li>Time(numericvalue[, format])</li>
<li>Timestamp(numericvalue[, format])</li>
<li>Interval(numericvalue[, format])</li>
</ul>
<br />
<div class="MsoNormal">
Where numeric value is the date value and format is the
optional format. If the formatted is omitted it will format the value to the
default for your environment/locale.</div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
These functions format only the text representation of the
date/time dual. <i style="mso-bidi-font-style: normal;">The underlying numeric
value is not affected</i>. So the text output of the following two expressions are
identical, but the values are not equal:</div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="mso-tab-count: 1;"> </span>Date(‘2014/07/10
09:00:00’, ‘YYYY/MM/DD’)<span style="mso-spacerun: yes;"> </span>= 41830.375<br />
<span style="mso-tab-count: 1;"> </span>Date(‘2017/07/10’,
‘YYYY/MM/DD’) = 41830.000</div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
I have also often seen constructions such as
Max(Date(someField)) or Date(someField)+2. The Date() format function is not
required in these expressions, and only serves to clutter the expression, and
in extreme cases may also affect performance.<span style="mso-tab-count: 1;"> </span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
The most important points to take away from this are: </div>
<br />
<ol>
<li><span style="font-family: Symbol; mso-bidi-font-family: Symbol; mso-fareast-font-family: Symbol;"><span style="mso-list: Ignore;"><span style="font: 7.0pt "Times New Roman";"></span></span></span>the value of the date is the numeric value of
the dual and </li>
<li>the formatting functions only affect the text
representation of<span style="mso-spacerun: yes;"> </span>the date/time</li>
</ol>
Jonathanhttp://www.blogger.com/profile/01169660018216478519noreply@blogger.com2tag:blogger.com,1999:blog-4667382907846182284.post-13009547397445969252014-06-14T12:29:00.003+02:002014-06-14T12:31:31.975+02:00About Table Concatenation<h4>
<span style="font-family: "Helvetica Neue",Arial,Helvetica,sans-serif;"><span style="font-size: small;">Concatenation</span></span></h4>
<span style="font-family: "Helvetica Neue",Arial,Helvetica,sans-serif;"><span style="font-size: small;"> According to the Qlikview Reference Manual:
</span></span><br />
<blockquote>
<span style="font-family: "Helvetica Neue",Arial,Helvetica,sans-serif;"><span style="font-size: small;">If two or more statements result in tables with identical field names, the tables are concatenated and treated as one logical table.</span></span></blockquote>
<span style="font-family: "Helvetica Neue",Arial,Helvetica,sans-serif;"><span style="font-size: small;">Let’s explore this statement. Consider the load script:</span></span><br />
<span style="font-family: "Helvetica Neue",Arial,Helvetica,sans-serif;"><span style="font-size: small;"><br /></span></span>
<span style="font-family: "Courier New",Courier,monospace;"><span style="font-size: small;">Table1:</span></span><br />
<span style="font-family: "Courier New",Courier,monospace;"><span style="font-size: small;">LOAD * Inline</span></span><br />
<span style="font-family: "Courier New",Courier,monospace;"><span style="font-size: small;">[</span></span><br />
<span style="font-family: "Courier New",Courier,monospace;"><span style="font-size: small;">A, B, C</span></span><br />
<span style="font-family: "Courier New",Courier,monospace;"><span style="font-size: small;">1, 2, 3</span></span><br />
<span style="font-family: "Courier New",Courier,monospace;"><span style="font-size: small;">4, 5, 6
];</span></span><br />
<span style="font-family: "Courier New",Courier,monospace;"><span style="font-size: small;"><br /></span></span>
<span style="font-family: "Courier New",Courier,monospace;"><span style="font-size: small;">Table2:</span></span><br />
<span style="font-family: "Courier New",Courier,monospace;"><span style="font-size: small;">LOAD * Inline</span></span><br />
<span style="font-family: "Courier New",Courier,monospace;"><span style="font-size: small;">[
A, B, C</span></span><br />
<span style="font-family: "Courier New",Courier,monospace;"><span style="font-size: small;">6, 5, 4</span></span><br />
<span style="font-family: "Courier New",Courier,monospace;"><span style="font-size: small;">4, 5, 6
];</span></span><br />
<span style="font-family: "Helvetica Neue",Arial,Helvetica,sans-serif;"><span style="font-size: small;"><br /></span></span>
<span style="font-family: "Helvetica Neue",Arial,Helvetica,sans-serif;"><span style="font-size: small;">If you run this script, there will only be one table – Table1. This table contains all 4 lines loaded, including the line 4, 5, 6, which will be loaded twice. Click File | Table Viewer (or Ctrl-T) and check the table contents in Table1.</span></span><br />
<span style="font-family: "Helvetica Neue",Arial,Helvetica,sans-serif;"><span style="font-size: small;"><br /></span></span>
<span style="font-family: "Helvetica Neue",Arial,Helvetica,sans-serif;"><span style="font-size: small;">Now if we add the lines:</span></span><br />
<span style="font-family: "Helvetica Neue",Arial,Helvetica,sans-serif;"><span style="font-size: small;"><br /></span></span>
<span style="font-family: "Courier New",Courier,monospace;"><span style="font-size: small;">Table3:</span></span><br />
<span style="font-family: "Courier New",Courier,monospace;"><span style="font-size: small;">LOAD * Resident Table1;</span></span><br />
<span style="font-family: "Helvetica Neue",Arial,Helvetica,sans-serif;"><span style="font-size: small;"><br /></span></span>
<span style="font-family: "Helvetica Neue",Arial,Helvetica,sans-serif;"><span style="font-size: small;">Table3 will not be created, as it contains the same fields as Table1 and will be concatenated. Now there will be 8 records in Table1.
So if the tables have the same fields, they will autoconcatenate. The field order is unimportant, but remember that QV field names are case sensitive.</span></span><br />
<span style="font-family: "Helvetica Neue",Arial,Helvetica,sans-serif;"><span style="font-size: small;"><br /></span></span>
<br />
<h4>
<span style="font-family: "Helvetica Neue",Arial,Helvetica,sans-serif;"><span style="font-size: small;">Avoiding Automatic Concatenation</span></span></h4>
<span style="font-family: "Helvetica Neue",Arial,Helvetica,sans-serif;"><span style="font-size: small;"></span></span>
<span style="font-family: "Helvetica Neue",Arial,Helvetica,sans-serif;"><span style="font-size: small;">If the table is created with different fields, then the second table will not be concatenated. But what if you need to reload the data from the resident table?
Add the lines</span></span><br />
<span style="font-family: "Helvetica Neue",Arial,Helvetica,sans-serif;"><span style="font-size: small;"><br /></span></span>
<span style="font-family: "Courier New",Courier,monospace;"><span style="font-size: small;">Table4:</span></span><br />
<span style="font-family: "Courier New",Courier,monospace;"><span style="font-size: small;">NoConcatenate</span></span><br />
<span style="font-family: "Courier New",Courier,monospace;"><span style="font-size: small;">LOAD * Resident Table1;</span></span><br />
<span style="font-family: "Helvetica Neue",Arial,Helvetica,sans-serif;"><span style="font-size: small;"><br /></span></span>
<span style="font-family: "Helvetica Neue",Arial,Helvetica,sans-serif;"><span style="font-size: small;">Now Table4 is not concatenated to Table1. (If the scripts end at this point we will have a synthetic key, but that is the subject of another post).</span></span><br />
<span style="font-family: "Helvetica Neue",Arial,Helvetica,sans-serif;"><span style="font-size: small;"><br /></span></span>
<span style="font-family: "Helvetica Neue",Arial,Helvetica,sans-serif;"><span style="font-size: small;">Note that if the second table is created with different fields (a subset of the first table’s fields), then it will not be concatenated, even subsequent statements add the missing field(s) or drop the extra fields. Look at this example:</span></span><br />
<span style="font-family: "Helvetica Neue",Arial,Helvetica,sans-serif;"><span style="font-size: small;"><br /></span></span>
<span style="font-family: "Courier New",Courier,monospace;"><span style="font-size: small;">Table1:</span></span><br />
<span style="font-family: "Courier New",Courier,monospace;"><span style="font-size: small;">LOAD * Inline</span></span><br />
<span style="font-family: "Courier New",Courier,monospace;"><span style="font-size: small;">[
A, B, C</span></span><br />
<span style="font-family: "Courier New",Courier,monospace;"><span style="font-size: small;">1, 2, 3</span></span><br />
<span style="font-family: "Courier New",Courier,monospace;"><span style="font-size: small;">2, 5, 6</span></span><br />
<span style="font-family: "Courier New",Courier,monospace;"><span style="font-size: small;">3, 6, 7
];</span></span><br />
<span style="font-family: "Courier New",Courier,monospace;"><span style="font-size: small;"><br /></span></span>
<span style="font-family: "Courier New",Courier,monospace;"><span style="font-size: small;">Table2:</span></span><br />
<span style="font-family: "Courier New",Courier,monospace;"><span style="font-size: small;">LOAD A, B</span></span><br />
<span style="font-family: "Courier New",Courier,monospace;"><span style="font-size: small;">Resident Table1;</span></span><br />
<span style="font-family: "Courier New",Courier,monospace;"><span style="font-size: small;"><br /></span>
<span style="font-size: small;"> Join(Table2)</span></span><br />
<span style="font-family: "Courier New",Courier,monospace;"><span style="font-size: small;">LOAD A, (A+B) As C</span></span><br />
<span style="font-family: "Courier New",Courier,monospace;"><span style="font-size: small;">Resident Table1;</span></span><br />
<span style="font-family: "Helvetica Neue",Arial,Helvetica,sans-serif;"><span style="font-size: small;"><br /></span></span>
<span style="font-family: "Helvetica Neue",Arial,Helvetica,sans-serif;"><span style="font-size: small;">Table2 will be created as a new table because it contains only a subset of Table1’s fields when it is created. This does not change when we add a column C to the table as the table already exists.</span></span><br />
<span style="font-family: "Helvetica Neue",Arial,Helvetica,sans-serif;"><span style="font-size: small;"><br /></span></span>
<span style="font-family: "Helvetica Neue",Arial,Helvetica,sans-serif;"><span style="font-size: small;">So the manual text should more accurately say:</span></span><br />
<span style="font-family: "Helvetica Neue",Arial,Helvetica,sans-serif;"><span style="font-size: small;"></span></span>
<blockquote>
<span style="font-family: "Helvetica Neue",Arial,Helvetica,sans-serif;"><span style="font-size: small;">If two or more statements create tables with identical field names, the tables are concatenated and treated as one logical table.</span></span></blockquote>
Jonathanhttp://www.blogger.com/profile/01169660018216478519noreply@blogger.com2tag:blogger.com,1999:blog-4667382907846182284.post-84135642019159394912014-01-12T22:04:00.000+02:002014-01-12T22:20:24.311+02:00Understanding Aggr()<span style="font-family: "Trebuchet MS",sans-serif;">Aggr() is an important part of the toolkit that you need when building more complex expressions and calculated dimensions in Qlikview. This post attempts to demystify aggr() and show it can be used.<br /> </span><br />
<span style="font-family: "Trebuchet MS",sans-serif;">In a Qlikview chart, such as a straight table, the chart expressions are evaluated for all possible combinations of the dimension. An Aggr() function does the much same, so the expression:<br /><br /><span style="font-family: "Courier New",Courier,monospace;">Aggr(Sum(Sales), Region)</span><br /><br />Produces a list of the summed values, one for each region. And this:<br /><br /><span style="font-family: "Courier New",Courier,monospace;">Aggr(Sum(Sales), Region, Period)</span></span><br />
<span style="font-family: "Trebuchet MS",sans-serif;"><span style="font-family: "Courier New",Courier,monospace;"> </span><br />Produces an array of sales for each region/period combination. Three dimensions would construct a cube, and so on. </span><br />
<br />
<span style="font-family: "Trebuchet MS",sans-serif;">You may use the aggr() expression anywhere that expects a list or array of values (technically an n-dimensional hypercube). This may include in a list box, a calculated dimension or in a nested aggregation. </span><br />
<span style="font-family: "Trebuchet MS",sans-serif;"><br />A nested aggregation is where one aggregation function (eg Sum, Min, Avg etc, not to be confused with the aggr() function) is contained within another. For example, lets say you would like to know the lowest possible summed value (eg Min(Sum(Sales))) for the sales outlets. Sum(Sales) will return a single value, but you need a list of Sum(Sales) by store. This is where aggr() comes in. The expression becomes <br /><br /><span style="font-family: "Courier New",Courier,monospace;">Min(Aggr(Sum(Sales), Store))</span> </span><br />
<br />
<span style="font-family: "Trebuchet MS",sans-serif;">For another example, Lets say you want to know the lower 5% and upper 95% percentile of sales by Store:<br /><br /><span style="font-family: "Courier New",Courier,monospace;">Fractile(Aggr(Sum(Sales), Store), 0.05)<br />Fractile(Aggr(Sum(Sales), Store), 0.95)</span><br /><br />If the data was arranged in months, and you wanted the expression should be calculated to each month (in a chart with month as a dimension):<br /><br /><span style="font-family: "Courier New",Courier,monospace;">Min(Aggr(Sum(Sales), Store, Month))<br />Fractile(Aggr(Sum(Sales), Store, Month), 0.05)<br />Fractile(Aggr(Sum(Sales), Store, Month), 0.95)</span></span><br />
<span style="font-family: "Trebuchet MS",sans-serif;"><span style="font-family: "Courier New",Courier,monospace;"> </span><br />If you have more than one year, you would of course need to have a single year selected before using this chart/table, or you could do something like:<br /><br /><span style="font-family: "Courier New",Courier,monospace;">Min(Aggr(Sum({</span><year ax="" ear=""><span style="font-family: "Courier New",Courier,monospace;">} Sales), Store, Month))<br />Fractile(Aggr(Sum({</span><year ax="" ear=""><span style="font-family: "Courier New",Courier,monospace;">} Sales), Store, Month), 0.05)<br />Fractile(Aggr(Sum({</span><year ax="" ear=""><span style="font-family: "Courier New",Courier,monospace;">} Sales), Store, Month), 0.95)</span><br /><br /><b>Aggr() expressions in a chart</b></year></year></year></span><br />
<span style="font-family: "Trebuchet MS",sans-serif;"><b> </b><br />When you use an aggr() in a chart, it is important to always include ALL the chart dimensions in the aggr() dimensions. This allows the expression to be calculated correctly in each cell in the chart or table.</span><br />
<span style="font-family: "Trebuchet MS",sans-serif;"><br />This can present a problem if you are using calculated dimensions, as the dimensions in the aggr() statement have to be fields, not expressions. If the calculated dimension is simple and based on a single field, and each value corresponds to a single value of that field, then you may be able to use the field name in the aggr() expression.</span>Jonathanhttp://www.blogger.com/profile/01169660018216478519noreply@blogger.com2tag:blogger.com,1999:blog-4667382907846182284.post-14252606715083825832013-01-06T18:01:00.001+02:002013-01-07T22:33:07.640+02:00Paged charts in QlikviewQlikview does not have a built-in paging mechanism for charts and tables. By paging, I mean returning a portion of the results (say top 5 salespeople), with "next" and "previous" buttons to view other portions - in the same way a web search with Google or Bing returns one page at a time.<br />
<br />
To illustrate, this is a simple paged chart in Qlikview:<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhzEjGf305ynsNlMytiP8DnHbjwgL-EHsH2sLi5iwMw8xfuf6ptFt8qquOJ09_lFQ6b02EqQUnByDqabY_b7UN_5JnFbehIPKjY8YTcnde1MpbeoZpsCZh1O8yTMQbog1ftWErXyxnQds0/s1600/Capture.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhzEjGf305ynsNlMytiP8DnHbjwgL-EHsH2sLi5iwMw8xfuf6ptFt8qquOJ09_lFQ6b02EqQUnByDqabY_b7UN_5JnFbehIPKjY8YTcnde1MpbeoZpsCZh1O8yTMQbog1ftWErXyxnQds0/s1600/Capture.PNG" height="220" width="320" /></a></div>
<br />
Here's how to do it.<br />
<ul>
<li>Create the following variables</li>
<ul>
<li>vRows - contains the number of rows to display per page - 5 in this case</li>
<li>vPage - the page number. Initialise to 1 for the first page.</li>
<li>vRankFrom - the expression <span style="font-size: x-small;"><span style="font-family: "Courier New",Courier,monospace;">=(vPage-1)*vRows+1</span></span></li>
<li>vRankTo - the expression <span style="font-size: x-small;"><span style="font-family: "Courier New",Courier,monospace;"> =vRankFrom+vRows-1</span></span></li>
<li>vPageCount - the expression <span style="font-size: x-small;"><span style="font-family: "Courier New",Courier,monospace;"> =Ceil(Count({1}DISTINCT Name)/vRows)</span></span></li>
<li><i>Note the = sign on the three expressions</i></li>
</ul>
<table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody>
<tr><td style="text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhLn9Uz52eV_-A-441223ksmTkhZpeEElo0DksZnKjLMJlAjcTK3EGtdzxlBV5pvjhJYAws-QaGfnHrZnDUda4o1Ld7vpfwzZ1K6Z04Hw48DDeC2rvLjGDtzBRbofBxVaXYUz5CbvNltrc/s1600/Capture.PNG" imageanchor="1" style="margin-left: auto; margin-right: auto;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhLn9Uz52eV_-A-441223ksmTkhZpeEElo0DksZnKjLMJlAjcTK3EGtdzxlBV5pvjhJYAws-QaGfnHrZnDUda4o1Ld7vpfwzZ1K6Z04Hw48DDeC2rvLjGDtzBRbofBxVaXYUz5CbvNltrc/s1600/Capture.PNG" height="141" width="320" /></a></td></tr>
<tr><td class="tr-caption" style="text-align: center;">Set these variables</td></tr>
</tbody></table>
<ul> </ul>
<ul> </ul>
<ul> </ul>
<li>Create 3 text boxes</li>
<ul>
<li>"Previous" or "<<" </li>
<ul>
<li>Lable the first one "<<" or "Prev" (<i>or whtaver makes sense in your situation</i>). </li>
<li>Create a set variable action for vPage with the following expression: <span style="font-family: "Courier New",Courier,monospace;"><span style="font-size: x-small;"><br />=RangeMax(vPage-1, 1)</span></span>.<br /><i>The RangeMax ensures that vPage will not be less than 1</i>.</li>
<li>Optionally set the font colour to black, and add the following calculate colour expression:<br /><span style="font-size: x-small;"><span style="font-family: "Courier New",Courier,monospace;">=If(vPage=1, White())</span></span><br />(<i>Set to whatever colours you like to indicate active and disabled states</i>)</li>
</ul>
</ul>
</ul>
<ul><ul>
<li>"Next" or ">>"</li>
<ul>
<li>As above, but use <span style="font-size: x-small;"><span style="font-family: "Courier New",Courier,monospace;">=RangeMin(vPage+1,vPageCount)</span></span> for the vPage set variable action and <span style="font-size: x-small;"><span style="font-family: "Courier New",Courier,monospace;">=If(vPage>=vPageCount, White())</span></span> for the calculated colour expression.</li>
</ul>
<li>Page lable - this is a simple textbox displaying the expression: <br /><span style="font-size: x-small;"><span style="font-family: "Courier New",Courier,monospace;">='Page ' & vPage & ' of ' & Ceil(Count(Distinct Name) / vRows)</span></span></li>
</ul>
<li>Create the chart. We need a chart with some sort of ordering such as
ranking salespeople by sales volumes. In the example the chart
expression is simply <span style="font-size: x-small;"><span style="font-family: "Courier New",Courier,monospace;">Sum(Value)</span></span>, and we will sort by this value. The dimension is the field [Name]. To adapt it to work with the ranking, use the following calculated dimension to limit the values to the current page:<br /><br /><span style="font-family: "Courier New",Courier,monospace;"><span style="font-size: x-small;">=Aggr(If(Rank(Aggr(<span style="color: red;">Sum(Value)</span>, Name)) >= <span style="color: magenta;">vRankFrom </span>And Rank(Aggr(<span style="color: red;">Sum(Value)</span>, Name)) <= <span style="color: magenta;">vRankTo</span>, <span style="color: blue;">Name</span>), Name)<br /><span style="font-family: Arial,Helvetica,sans-serif;"><span style="font-size: small;"><br /></span></span></span></span>(<span style="font-size: small;"><i><span style="font-size: small;">The ranking functions </span>wrap the chart express<span style="font-size: small;">ion (red) returning the dimension values (blue) tha<span style="font-size: small;">t fall inside the range specified vt the vRankFrom and vRankTo variables</span></span></i></span>). </li>
<li>On the sort tab, select Sort by "Y-value" descending.</li>
<li>One the Presentation tab, check the "Max Visible Number" box and enter the expression<span style="font-size: x-small;"><span style="font-family: "Courier New",Courier,monospace;"> =vRows</span></span> in the expression box.</li>
</ul>
Here is a QV document that implements the paging described above:<br />
<br />
<a href="https://docs.google.com/open?id=0BzFdSk9UQtT8QTI2cElhWXlEMzQ">DataPaging.qvw</a><br />
<br />
<br />
<table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody>
<tr><td style="text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiLTGy3OHutGSDN-7fUZcROzhvNeVAIozCSckXmAuWdjtCvT-FnJPll9nT0Z7n-skYf5OZmlXeXdBCQF5K2P3KUVTJQHRC7i63LDPkBoJqTdiCyQ8cFZG_CIDMxVt9SAyfX08PN1mkrYy8/s1600/Capture.PNG" imageanchor="1" style="margin-left: auto; margin-right: auto;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiLTGy3OHutGSDN-7fUZcROzhvNeVAIozCSckXmAuWdjtCvT-FnJPll9nT0Z7n-skYf5OZmlXeXdBCQF5K2P3KUVTJQHRC7i63LDPkBoJqTdiCyQ8cFZG_CIDMxVt9SAyfX08PN1mkrYy8/s1600/Capture.PNG" height="141" width="320" /></a></td></tr>
<tr><td class="tr-caption" style="text-align: center;"><i>Action for previous button</i></td></tr>
</tbody></table>
<table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody>
<tr><td style="text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjYXShZ89sCgyR3vDeEMnORnLoTVrgPNsrb7PanYu1AnyPQv6-z2Aq1ZTOZalPYGv_a_OznGAQBmEwEr6T3alZPzqpRN-nOQWYtClHAKbbEaBNH1IVfa5EXHvr2j4p-Vb0EO4BrO2VMoGY/s1600/Capture.PNG" imageanchor="1" style="margin-left: auto; margin-right: auto;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjYXShZ89sCgyR3vDeEMnORnLoTVrgPNsrb7PanYu1AnyPQv6-z2Aq1ZTOZalPYGv_a_OznGAQBmEwEr6T3alZPzqpRN-nOQWYtClHAKbbEaBNH1IVfa5EXHvr2j4p-Vb0EO4BrO2VMoGY/s1600/Capture.PNG" height="162" width="320" /></a></td></tr>
<tr><td class="tr-caption" style="text-align: center;">Colour settings for previous button</td></tr>
</tbody></table>
<br />
<table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody>
<tr><td style="text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhT4kM1Y0JzJpNkjrK5KiKQqW2KAF4xXSAo0p0IIqFSxEZToc1BhZV9Oi_rLFLjRWWqAj-3dDABHCM-i3tP8T7UkpK47UAfqol8vXF3_qjnq-2mrfwt2G1FoVThX_hXSPsM4gC9DaKTetI/s1600/Capture.PNG" imageanchor="1" style="margin-left: auto; margin-right: auto;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhT4kM1Y0JzJpNkjrK5KiKQqW2KAF4xXSAo0p0IIqFSxEZToc1BhZV9Oi_rLFLjRWWqAj-3dDABHCM-i3tP8T7UkpK47UAfqol8vXF3_qjnq-2mrfwt2G1FoVThX_hXSPsM4gC9DaKTetI/s1600/Capture.PNG" height="131" width="400" /></a></td></tr>
<tr><td class="tr-caption" style="text-align: center;">Max Visible Number setting on Presentation tab</td></tr>
</tbody></table>
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />Jonathanhttp://www.blogger.com/profile/01169660018216478519noreply@blogger.com5tag:blogger.com,1999:blog-4667382907846182284.post-59564607115305287862013-01-05T17:49:00.001+02:002014-01-12T22:07:15.295+02:00Handy data discovery toolHere's a handy tool to assist in the early development and analysis of the data coming into your Qlikview documents. There are three objects that you can copy from the attached Qlikview document and paste into your document. You do not need to modify your data model or load script in any way as these objects use standard QV built-in fuctionality.<br />
<br />
<ul>
<li>A tree-view list box with the tables and fields in your data model</li>
<li>A text box containing summary statistics for the selected field (hidden until a field is selected)</li>
<li>A dynamic list box containing the distinct values of the selected field (also hidden until a field is selected)</li>
</ul>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgb4kDzQq_paTxBu0HpeldvaOQ-8h5FsOPIVJbZalb9OqFnIydeXtD8wXtNSAJZ5x4RnWaUNhF-wcBZN9pfdgopAoERbLrLsRug4I4S9tlIREQWAya4MfkhRsljn_3bA1MhPyuXKoYJpxw/s1600/Capture.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgb4kDzQq_paTxBu0HpeldvaOQ-8h5FsOPIVJbZalb9OqFnIydeXtD8wXtNSAJZ5x4RnWaUNhF-wcBZN9pfdgopAoERbLrLsRug4I4S9tlIREQWAya4MfkhRsljn_3bA1MhPyuXKoYJpxw/s1600/Capture.PNG" height="309" width="320" /></a></div>
<br />
Here is a Qlikview document containing the 3 objects. Select a field name (any one) to make all three visible and copy from tis document into your Qlikview document.<br />
<br />
<a href="https://docs.google.com/open?id=0BzFdSk9UQtT8eFdqR2REM2VPYXM">Data Discovery.qvw</a><br />
<br />
<h4>
<span style="font-size: small;">Creating the tools yourself</span></h4>
If you are using QV Personal Edition, you will not be able to open the attached document, so here is how you can create these objects<br />
<br />
<h4>
The data structure listbox</h4>
Create a list box, select <expression> in the Field box, and enter the following expression:</expression><br />
<br />
<span style="font-size: x-small;"><span style="font-family: "Courier New",Courier,monospace;">=Aggr(Only({1} $Table) & '|' & Only({1} $Field), $Field, $Table) </span></span><br />
<br />
Then check the "Show as TreeView" option and enter the vertical pipe "|" as the separator.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiUwy406m9qlWZSaDlgHuX8lOgUnm132Eyv2iZ8CGNJD78-RcP265zyU8thtaNCNGEADku_B02OOkCkFoMzUBT365-0NgVZyE1dfBF_ZRNlnKlQ1iEPR44sw4SHLGr_9SY9NPZAM_9VH1I/s1600/Capture2.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiUwy406m9qlWZSaDlgHuX8lOgUnm132Eyv2iZ8CGNJD78-RcP265zyU8thtaNCNGEADku_B02OOkCkFoMzUBT365-0NgVZyE1dfBF_ZRNlnKlQ1iEPR44sw4SHLGr_9SY9NPZAM_9VH1I/s1600/Capture2.PNG" height="271" width="320" /></a></div>
<br />
<h4>
The text box</h4>
Create a text box, and add the following expression:<br />
<br />
<span style="font-size: x-small;"><span style="font-family: "Courier New",Courier,monospace;">=Num(Count(Distinct [$(=$Field)]), '# ##0') & ' unique values<br />' & Num(Count([$(=$Field)]), '# ##0') & ' total values ('<br /> & Num(Count([$(=$Field)]) / (Count([$(=$Field)]) + NullCount([$(=$Field)])), '0%') & ')<br />' & Num(NullCount([$(=$Field)]), '# ##0') & ' null rows<br />' & Num(Count(If(Len([$(=$Field)])=0, [$(=$Field)])), '# ##0') & ' empty values' </span></span><br />
<br />
On the Layout tab, add the following conditional expression:<br />
<span style="font-size: x-small;"><span style="font-family: "Courier New",Courier,monospace;">Count($Field) = 1</span></span><br />
<br />
<h3>
The dynamic list box</h3>
<br />
Create a list box, select <expression> and add the expression:</expression><br />
<span style="font-size: x-small;"><span style="font-family: "Courier New",Courier,monospace;">=[$(=$Field)]</span></span><br />
<br />
On the Layout tab, add the following conditional expression:<br />
<span style="font-size: x-small;"><span style="font-family: "Courier New",Courier,monospace;">Count($Field) = 1</span></span><br />
<br />
<h3>
Conclusion</h3>
Remember to select a field in the data discovery list box to see the other two objects. Feel free to use and modify the objects in any way you please. With all the usual disclaimers...<br />
<br />
<br />
<br />
<br />
<br />
<br />
<h4>
</h4>
<br />Jonathanhttp://www.blogger.com/profile/01169660018216478519noreply@blogger.com3tag:blogger.com,1999:blog-4667382907846182284.post-40133598082592590412011-10-16T15:03:00.001+02:002011-10-16T15:03:57.048+02:00Dual values for “self-sorting”<span style="font-size:11pt;font-family:Arial;color:#000000;background-color:transparent;font-weight:bold;font-style:normal;font-variant:normal;text-decoration:none;vertical-align:baseline;" id="internal-source-marker_0.19763322232861935"></span><span style="font-size:10pt;font-family:Arial;color:#000000;background-color:transparent;font-weight:normal;font-style:normal;font-variant:normal;text-decoration:none;vertical-align:baseline;">Sometimes you need a field that has a unusual sort order. For example, you might have an a range field that contains values <0, 0-10, 10-100, >100. When you use this field as a dimension in a table or chart, you will want to sort this in the order above. In many cases, this can be done by using the “Load Order” option, but there are cases where the natural load order may not work (calculated fields, for example).</span><br /><span style="font-size:10pt;font-family:Arial;color:#000000;background-color:transparent;font-weight:normal;font-style:normal;font-variant:normal;text-decoration:none;vertical-align:baseline;"></span><br /><span style="font-size:10pt;font-family:Arial;color:#000000;background-color:transparent;font-weight:normal;font-style:normal;font-variant:normal;text-decoration:none;vertical-align:baseline;">The load order is also of no use if you plan to use the field in a rank expression. </span><br /><span style="font-size:10pt;font-family:Arial;color:#000000;background-color:transparent;font-weight:normal;font-style:normal;font-variant:normal;text-decoration:none;vertical-align:baseline;"></span><br /><span style="font-size:10pt;font-family:Arial;color:#000000;background-color:transparent;font-weight:normal;font-style:normal;font-variant:normal;text-decoration:none;vertical-align:baseline;">In these case, one option is to create dual values for the field. A dual value contains a text representation and numeric value. Dates are numeric, containing the formatted date and numeric date representation. They display the formatted date, but sort using the numeric value. You can also perform arithmetic on the numeric value. But did you know that you can create your own, custom, dual values?</span><br /><span style="font-size:10pt;font-family:Arial;color:#000000;background-color:transparent;font-weight:normal;font-style:normal;font-variant:normal;text-decoration:none;vertical-align:baseline;"></span><br /><span style="font-size:10pt;font-family:Arial;color:#000000;background-color:transparent;font-weight:normal;font-style:normal;font-variant:normal;text-decoration:none;vertical-align:baseline;">To do this, use the Dual() function. This is an example of a calculated field using Dual:</span><br /><span style="font-size:11pt;font-family:Arial;color:#000000;background-color:transparent;font-weight:normal;font-style:normal;font-variant:normal;text-decoration:none;vertical-align:baseline;"></span><br /><span style="font-size:9pt;font-family:Courier New;color:#000000;background-color:transparent;font-weight:normal;font-style:normal;font-variant:normal;text-decoration:none;vertical-align:baseline;">LOAD</span><br /><p dir="ltr" style="margin-left: 36pt; margin-top: 0pt; margin-bottom: 0pt;"><span style="font-size:9pt;font-family:Courier New;color:#000000;background-color:transparent;font-weight:normal;font-style:normal;font-variant:normal;text-decoration:none;vertical-align:baseline;">...</span></p><p dir="ltr" style="margin-left: 36pt; margin-top: 0pt; margin-bottom: 0pt;"><span style="font-size:9pt;font-family:Courier New;color:#000000;background-color:transparent;font-weight:normal;font-style:normal;font-variant:normal;text-decoration:none;vertical-align:baseline;">If(IsNull(PaymentAmount) Or PaymentAmount = 0, Dual('None', 0),</span></p><p dir="ltr" style="margin-left: 36pt; margin-top: 0pt; margin-bottom: 0pt;"><span style="font-size:9pt;font-family:Courier New;color:#000000;background-color:transparent;font-weight:normal;font-style:normal;font-variant:normal;text-decoration:none;vertical-align:baseline;"> If(PaymentAmount > 0.9 * ExpectedInstallment Or IsExempted, Dual('>90%', 90),</span></p><p dir="ltr" style="margin-left: 36pt; margin-top: 0pt; margin-bottom: 0pt;"><span style="font-size:9pt;font-family:Courier New;color:#000000;background-color:transparent;font-weight:normal;font-style:normal;font-variant:normal;text-decoration:none;vertical-align:baseline;"> If(PaymentAmount > 0.5 * ExpectedInstallment, Dual('50 - 90%', 50),</span></p><p dir="ltr" style="margin-left: 36pt; margin-top: 0pt; margin-bottom: 0pt;"><span style="font-size:9pt;font-family:Courier New;color:#000000;background-color:transparent;font-weight:normal;font-style:normal;font-variant:normal;text-decoration:none;vertical-align:baseline;"> If(PaymentAmount > 0 Or InSuspense, Dual('0% - 50%', 1),</span></p><p dir="ltr" style="margin-left: 36pt; margin-top: 0pt; margin-bottom: 0pt;"><span style="font-size:9pt;font-family:Courier New;color:#000000;background-color:transparent;font-weight:normal;font-style:normal;font-variant:normal;text-decoration:none;vertical-align:baseline;"> Dual('Reversal', -1))))) As PayGroup,</span></p><p dir="ltr" style="margin-left: 36pt; margin-top: 0pt; margin-bottom: 0pt;"><span style="font-size:9pt;font-family:Courier New;color:#000000;background-color:transparent;font-weight:normal;font-style:normal;font-variant:normal;text-decoration:none;vertical-align:baseline;">…</span></p><span style="font-size:9pt;font-family:Courier New;color:#000000;background-color:transparent;font-weight:normal;font-style:normal;font-variant:normal;text-decoration:none;vertical-align:baseline;">FROM …</span><br /><span style="font-size:11pt;font-family:Arial;color:#000000;background-color:transparent;font-weight:normal;font-style:normal;font-variant:normal;text-decoration:none;vertical-align:baseline;"></span><br /><span style="font-size:10pt;font-family:Arial;color:#000000;background-color:transparent;font-weight:normal;font-style:normal;font-variant:normal;text-decoration:none;vertical-align:baseline;">In this example, the load order does not reflect the required sort order, but values of Paygroup will sort according to the numeric value:</span><br /><span style="font-size:10pt;font-family:Arial;color:#000000;background-color:transparent;font-weight:normal;font-style:normal;font-variant:normal;text-decoration:none;vertical-align:baseline;"></span><br /><span style="font-size:10pt;font-family:Arial;color:#000000;background-color:transparent;font-weight:normal;font-style:normal;font-variant:normal;text-decoration:none;vertical-align:baseline;"> Reversal, None, 0% - 50%, 50% - 90%, >90%</span><br /><span style="font-size:10pt;font-family:Arial;color:#000000;background-color:transparent;font-weight:normal;font-style:normal;font-variant:normal;text-decoration:none;vertical-align:baseline;"></span><br /><span style="font-size:10pt;font-family:Arial;color:#000000;background-color:transparent;font-weight:normal;font-style:normal;font-variant:normal;text-decoration:none;vertical-align:baseline;">Just remember to ensure that the “Numeric Value” sort option is selected when you need this sort order.</span><br /><span style="font-size:10pt;font-family:Arial;color:#000000;background-color:transparent;font-weight:normal;font-style:normal;font-variant:normal;text-decoration:none;vertical-align:baseline;"></span>Jonathanhttp://www.blogger.com/profile/01169660018216478519noreply@blogger.com1tag:blogger.com,1999:blog-4667382907846182284.post-20126144291463107522011-08-21T11:07:00.004+02:002011-08-21T11:32:56.803+02:00Howto: Create a Waterfall ChartA waterfall chart is a type of cumulative bar chart, where each bar begins at the previous cumulative total and has a length equal to the value at that dimension (positive or negative). Something like this:
<br />
<br /><a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg2K-qwzHuRFPU_oxayQyNnEwranOIoHyyY-zWUl8cf7iFrg4b0BE0xh5RMX65wMSLXiAzrW_3e_dGoAy5AydY_MuBpnsCQs75v4e-wN5-0f70QLZN20X0xls95p0rvL_q9nmUwQ3t5y4I/s1600/Capture.PNG"><img style="display:block; margin:0px auto 10px; text-align:center;cursor:pointer; cursor:hand;width: 320px; height: 266px;" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg2K-qwzHuRFPU_oxayQyNnEwranOIoHyyY-zWUl8cf7iFrg4b0BE0xh5RMX65wMSLXiAzrW_3e_dGoAy5AydY_MuBpnsCQs75v4e-wN5-0f70QLZN20X0xls95p0rvL_q9nmUwQ3t5y4I/s320/Capture.PNG" alt="" id="BLOGGER_PHOTO_ID_5643234197970352722" border="0" /></a>
<br />So, how do we go about this.
<br />
<br /><span style=" font-weight: bold;font-family:trebuchet ms;" >CREATE BAR CHART</span>
<br />Create a bar chart with the dimension you require for the X axis, and the following expressions:<ul><li>Total (this is the value for the final, total bar - green in the example above)
<br /></li><li>Value (this is the value for the blue and red bars - rename as appropriate)
<br /></li><li>Cum (this is a hidden value which tracks the cumulative value)
<br /></li></ul>For example, let's have a simple data set with fields Dim1 and Value1. There is a dummy Total with Dim1 = Total, Value1 = 0.
<br />
<br />Use the expressions:
<br />
<br /><ul><li><span style="font-family:courier new;">Total: If(Dim1 = 'Total', Sum(Total Value1))</span></li><li><span style="font-family:courier new;">Value: Sum(Value1)</span></li><li><span style="font-family:courier new;">Cum: RangeSum(Above(Column(3)), Column(2))</span></li></ul>
<br />Set Cum to Invisible on the Expressions tab of the chart properties.
<br />
<br /><span style=" font-weight: bold;font-family:trebuchet ms;" >MAKE THE WATERFALL</span>
<br />
<br />The trick now is to use the bar offset on the Value expression and use this expression:
<br />
<br /><span style="font-family:courier new;">=If(IsNull(Above(Column(3))), 0, Above(Column(3)))+if(Column(2)<0,Column(2),0)</span>
<br />
<br />Click the + sign next to the expression name in the Expressions tab to see the Bar Offset.
<br />
<br />That should do it. You can download a demo here : <a href="https://docs.google.com/leaf?id=0BzFdSk9UQtT8OTRlZmM5ZmMtZjkxMy00NGRkLWE5NjQtYjRkODcxYzA3MTEx&hl=en_GB">Waterfall demo</a>
<br />
<br />To colour the negative values red, set the Background Color for Value to the expression:
<br />
<br /><span style="font-family: courier new;">=If(Column(2)<0, RGB(200,110,130))</span>
<br />
<br />Have fun!
<br />Jonathanhttp://www.blogger.com/profile/01169660018216478519noreply@blogger.com3tag:blogger.com,1999:blog-4667382907846182284.post-15270126697644670092011-05-30T20:40:00.003+02:002011-05-30T20:48:07.641+02:00Faulty SortingI recently had a problem some straight table charts in a model that would allow sorting in only one direction. In other words, double clicking the header would sort by that header, but double clicking again would not reverse the sort direction. In fact, only the direction set in the Sort tab of the chart properties would work.<br /><br />The chart had normal dimensions and expressions and no apparent reason for this behaviour.<br /><br />The problem turned out to be that the first expression had been disabled. When I deleted this expression, sorting returned to the normal behaviour.<br /><br />The lesson I learned here was that if I want to disable an expression during model development, demote it so that it is the last rather than the first expression in the chart.<br /><br />Using QV 10 SR2Jonathanhttp://www.blogger.com/profile/01169660018216478519noreply@blogger.com1tag:blogger.com,1999:blog-4667382907846182284.post-69225493052014942342011-04-25T16:56:00.003+02:002011-04-25T17:01:40.343+02:00Understanding DualsQV stores all numeric data (numbers, dates, time, intervals, etc) in what is called “dual” format. Understanding dual format may help in overcoming some model pitfalls and may assist in solving some problems.<br /><br />In brief, a dual format value comprises and numeric value, and a text representation. These may be standard number or date formats, they may be built in series (eg months, days of the week) or any customised series (more on this in a later post). The text representation is the equivalent of a format, but it can be more than that.<br /><br />To illustrate, the number 40658 could represent:<br /><ul><li>The value 40658. Depending on the format this could be displayed as 40,658 or 40658.00 etc.</li><li>The date 18 April 2011.</li><li>A product (or customer or branch or region etc) code 0040658</li><li>etc</li></ul><br />The number 0.65 could also represent:<br /><ul><li>A percentage - 65%</li><li>The time of day 3:36 pm (0.65 of a day of 24 hours from midnight to midnight)</li><li>An interval of 15 hours 36 minutes (0.65 of 24 hours)</li><li>Etc</li></ul><br />The format can be created implicitly by QV. During a load, it will infer the format from the source data. If a certain column in a CSV data source contains numbers:<br /><br /><div style="text-align: center;">8.35000<br />9.15000<br />3.62333<br /><br /></div>Then QV will infer a format of 0.00000 for that field. Certain QV date and time functions also create the output in the default time and date format for your model.<br /><br /><span style="font-weight: bold;">Taking control – formatting commands</span><br /><br />By default, QV will display the text portion of the dual on text boxes, captions, dimension/expression labels and the like, and will use the numeric portion for arithmetic expressions.<br /><br />You can also manually control this with the Num() and Text() functions. For example, if a field “TimeElapsed” represents an interval with a value of 0.25 (6 hours), then Num(TimeElapsed) will return 0.25 and Text(TimeElapsed) will return 06:00:00 (assuming your default interval format is hh:mm:ss).<br /><br />Some functions (like Min, Max, Avg) only return the numeric part of the expression, others return a dual value - check the return type in the auto-completion prompts or the documentation.<br /><br />The formatting commands (Num(), Money(), Date(), Time(), TimeStamp(), and Interval()) allow you to control the text representation. It is worth pointing out that they have no effect on the numeric portion.<br /><br />For example, Date(40651.65, ‘YYYY/MM/DD’) will display 2011/04/18, but the numeric value is still 40651.65. In other words, the Date function does NOT truncate the fractional part of the value, it simply does not display it. (<span style="font-style: italic;">To remove the time from date/time values, use Floor()</span>).<br /><br />That provides a very simple conceptual overview of dual values and formatting in Qlikview and I hope it helps with your understanding of dual values.<br /><br />I will address some of “dual” issues in a little more depth in further posts on this site in the near future. I value your feedback and any topic suggestions or questions.Jonathanhttp://www.blogger.com/profile/01169660018216478519noreply@blogger.com0tag:blogger.com,1999:blog-4667382907846182284.post-59515789522599660422011-02-07T21:14:00.003+02:002011-02-07T21:24:03.920+02:00When Totals Don’t WorkQlikview (QV) provides for three types of totals in straight tables and pivot tables:<br /><ul><li>None</li><li>The expression calculated at the total level</li><li>Aggregate function (sum, average, minimum etc) of the table rows</li></ul><br />These options cover most cases, but sometimes you have a table where none of these options is appropriate, or provides the correct result. As an example, consider a table of absolute variances, such as budget amount vs selling amount, on a table dimensioned by product class. The expression for the variance and % variance would be:<br /><br /><span style="font-size:100%;"><span style="font-family: courier new;">[AbsoluteVariance] = Fabs(Sum(BudgetAmount) - Sum(SellingAmount))<br /><br /></span><span style="font-family: courier new;">[%AbsoluteVariance] = Fabs(Sum(BudgetAmount) - Sum(SellingAmount)) / Sum(BudgetAmount)</span><br /></span><br /><br />These expressions yield different results for a different degree of dimensioning, so the sum of the absolute variances per ProductClass is not the same as the absolute variance at the total level. Therefore, the AbsoluteVariance column needs to be totalled using the Sum of rows option. For the %AbsoluteVariance, however, the expression at the total level does not work for the same reason, and the summing of the rows is arithmetically meaningless. In this example, a total is required for both of these columns.<br /><br />So, how do we construct an expression that will produce the correct %AbsoluteVariance for each row and for the total?<br /><br />We have the expression for the rows:<br /><br /><span style="font-family: courier new;">[%AbsoluteVariance] = Fabs(Sum(BudgetAmount) - Sum(SellingAmount)) / Sum(BudgetAmount)</span><br /><br />For the total, we need an expression like:<br /><br />[%AbsoluteVariance] = Sum(Rows of AbsoluteVariance) / Sum(BudgetAmount)<br /><br />Assuming that ProductClass was the only dimension on the table, the Sum(Rows of AbsoluteVariance) can be calculated as follows:<br /><br /><span style="font-family: courier new;">Sum(Aggr(Fabs(Sum(BudgetAmount) – Sum(SellingAmount)), ProductClass))</span><br /><br />So, how do we differentiate between the two expressions?<br /><br />This is where the QV Dimensionality() function comes in. In our case, Dimensionality() will return zero when calculated on the total row, and greater than zero elsewhere. Check the Qlikview Reference Manual for more information on the function.<br /><br />Our final expression becomes:<br /><br /><span style="font-family: courier new;">If(Dimensionality() = 0, </span><br /><span style="font-family: courier new;"> <span style="color: rgb(0, 153, 0);">// total row expression</span></span><br /><span style="font-family: courier new;"> Sum(Aggr(Fabs(Sum(BudgetAmount) – Sum(SellingAmount)), ProductClass)) / Sum(BudgetAmount),</span><br /><span style="font-family: courier new;"> <span style="color: rgb(0, 153, 0);">// other row expression </span></span><br /><span style="font-family: courier new;"> Fabs(Sum(BudgetAmount) - Sum(SellingAmount)) / Sum(BudgetAmount))</span><br /><br /><span style="font-weight: bold;">QED</span><br /><br />Unzip the <a href="https://docs.google.com/leaf?id=0BzFdSk9UQtT8Y2VmNDliNDItZGI2YS00ZDRjLTgyYzctMWQwM2NiNzFhNDVj&hl=en_GB">download here</a> and play around with a sample QVW model and the Excel source file.Jonathanhttp://www.blogger.com/profile/01169660018216478519noreply@blogger.com1tag:blogger.com,1999:blog-4667382907846182284.post-58070336209327838192011-01-12T13:44:00.003+02:002011-01-12T14:08:25.251+02:00A Novel Bookmark Display<span style="font-family:trebuchet ms;">This post arose after a client asked for a view of document bookmarks that was similar to a horizontally arranged list box selector. The QV model had a number of states which could be selected using these bookmarks. He wanted his staff to be able to see the selected. The solution looked something like this:</span><br /><br /><a style="font-family: trebuchet ms;" onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjUiwz7zAU3KKMkxuEsYgxWsTDPWZPN8UD_5UUdc_q6a2WLdNevY_QUco5sgk5oxsLL-WTL3ffScjoZjTVCypTFFWBG7TOxz0tZ8d6QWVx2EMyQptW37pPXHNIpEpxB6khEWnVjiUMRgX8/s1600/Capture.PNG"><img style="display: block; margin: 0px auto 10px; text-align: center; cursor: pointer; width: 320px; height: 43px;" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjUiwz7zAU3KKMkxuEsYgxWsTDPWZPN8UD_5UUdc_q6a2WLdNevY_QUco5sgk5oxsLL-WTL3ffScjoZjTVCypTFFWBG7TOxz0tZ8d6QWVx2EMyQptW37pPXHNIpEpxB6khEWnVjiUMRgX8/s320/Capture.PNG" alt="" id="BLOGGER_PHOTO_ID_5561264196901192098" border="0" /></a><span style="font-family:trebuchet ms;">I<span style="font-size:100%;"> achieved this as follows:</span></span><span style="font-size:100%;"><br /></span><br /><br /><span style="font-family:trebuchet ms;"><span style="font-weight: bold;">Step 1: </span>Create the document bookmarks in the normal manner.<br /><br /><span style="font-weight: bold;">Step 2: </span>Open the bookmarks dialog (Bookmarks | More) to get the bookmark Ids for each bookmark. Then uncheck the checkboxes in the “+” column.<br /><br /><span style="font-weight: bold;">Step 3: </span>Create an inline table in the load script with the bookmark names and IDs – something like this:<br /></span><br /><br /><pre>LOAD * INLINE<br />[<br /> BMName, BMId<br /> Stock on hand, BM32<br /> Reclaim pending, BM30<br /> Legal hold, BM26<br /> Write offs, BM33<br /> Phyical stock, BM29<br /> Saleable stock, BM31<br />];<br /></pre><br /><br /><span style="font-family:trebuchet ms;"><span style="font-weight: bold;">Step 4: </span>Create a list box containing BMName, and arrange horizontally. You can also sort the list box by load order, if appropriate.<br /><br /><span style="font-weight: bold;">Step 5: </span>Add the BMName selection to each bookmark, by selecting the bookmark (from a bookmark control, or the menu. Then select the corresponding BMName value. Finally, click Bookmarks | Replace bookmark to update the bookmark with the BMName selection. I used an open Current Selections box to verify the correct behaviour.<br /><br /><span style="font-weight: bold;">Step 6: </span>(Almost there!) Go to Settings | Document Properties | Triggers and add an On Select trigger on the field BMName. Add a Clear All action, followed by an Apply Bookmark action. For the Bookmark ID for the latter, enter “=BMId” (without the quotes!).<br /><br />That’s it. Now selecting a value in the “selector bar” for the bookmarks will apply the bookmark and show the currently applied bookmark. Clicking the selected value will clear the bookmark (ie normal list box behaviour).<br /><br />If you find this useful, or would have any comments on this technique, please post a comment.<br /></span>Jonathanhttp://www.blogger.com/profile/01169660018216478519noreply@blogger.com10tag:blogger.com,1999:blog-4667382907846182284.post-59142434376568586942010-10-10T12:08:00.004+02:002010-10-14T08:44:30.482+02:00Qlikview Working Day Functions 3<p>This post concludes the short series on how to use the working day functions in Qlikview. (<a href="http://yahqblog.blogspot.com/2010/09/qlikview-working-day-functions.html">Start from the beginning</a>)<br /></p><h2 style="font-family: trebuchet ms; color: rgb(102, 102, 204);">Putting it together (IsWorkDay subroutine)</h2><p>The code below is a subroutine, callable from the load script, that evaluates a date and return true if it is a work day, and false if it is not.</p>Parameters:<br /><ul><li>testDate (in, date) - the date to test</li><li>rval (out, true/false) - true if the date is a work date, false otherwise.</li></ul>Variable vPublicHolidays is a string containing a comma separated list of public holidays. See the earlier post for more information.<br /><p><span style="color: rgb(0, 102, 0);font-family:courier new;font-size:100%;" ><br /><pre>Sub IsWorkDay(rval, testDate)<br />Let zTest = NetWorkDays(MonthStart(testDate), testDate,<br />$(vPublicHolidays));<br /><br /><span style="color: rgb(153, 153, 153); font-style: italic;"> // If the day is a non-working day, and there are no</span><br /><span style="color: rgb(153, 153, 153); font-style: italic;"> // working days before it in the current month,<br />// NetWorkDays will return 0 </span><br />If zTest = 0 Then<br />rval = false();<br /><br /><span style="color: rgb(153, 153, 153); font-style: italic;"> // If the day is a working day, and/or there are working</span><br /><span style="color: rgb(153, 153, 153); font-style: italic;"> // days before it, NetWorkDays will return > 0</span><br />Else<br /><span style="font-style: italic; color: rgb(153, 153, 153);"> // If the date is the first day of month then it must<br /> // be a work day</span><br />If DayStart(testDate) = MonthStart(testDate) Then<br /> rval = true();<br /><span style="font-style: italic; color: rgb(153, 153, 153);"> // Else compare with NetWorkDay for yesterday. If </span><br /><span style="font-style: italic; color: rgb(153, 153, 153);"> // different, then the date is a work day</span><br />Else<br /> rval = If((zTest <> NetWorkDays(MonthStart(testDate),<br /> testDate-1, $(vPublicHolidays))),<br /> true(), false());<br />End If<br />End If<br />End Sub;<br /></pre></span></p><p>Put this script on a separate tab on your load script, or in an include file. Then call it like this:</p><p><span style="color: rgb(0, 102, 0);font-family:courier new;font-size:100%;" ><pre><br />Let vTheDate = Date#('2010/09/22', 'YYYY/MM/DD');<br />Call IsWorkDay(vTheDate, rval);<br />If rval Then<br /><span style="font-style: italic;">... do something if it is a work date</span><br />Else<br /><span style="font-style: italic;">... do something if it is not a work date</span><br />End If<br /></pre></span></p><p>The <a href="http://yahqblog.blogspot.com/2010/09/qlikview-working-day-functions.html">series start page is here</a>.</p>Jonathanhttp://www.blogger.com/profile/01169660018216478519noreply@blogger.com0tag:blogger.com,1999:blog-4667382907846182284.post-83570738090039188702010-09-23T08:46:00.008+02:002010-10-14T08:44:53.311+02:00Qlikview Working Day Functions 2<p>This post continues the short series on how to use the working day functions in Qlikview. (<a href="http://yahqblog.blogspot.com/2010/09/qlikview-working-day-functions.html">Start from the beginning</a>)<br /></p><h2 style="font-family: trebuchet ms; color: rgb(102, 102, 204);">LastWorkDate([start date], n)</h2><p>This function calculates the date of the nth working day after the start date. The start date is work day number 1 if it is a working day and if the start date is a non-working day, the function will return the next working day for n = 1.</p><p>Calculate the date of the nth working day of this month:</p><p style="color: rgb(0, 153, 0);font-family:courier new;">=<span style="font-weight: bold;">LastWorkDate</span>(MonthStart(Today()), n)</p><p><i>eg</i> <b>LastWorkDate</b>(MonthStart('2010/09/15'), 11) = '2010/09/15', so working day 11 of the month is 15 September 2010.</p><h2 style="font-family: trebuchet ms; color: rgb(102, 102, 204);">FirstWorkDate([start date], n)</h2><p>This function calculates the date of the nth working day before the start date. The start date is work day number 1 if it is a working day and if the start date is a non-working day, the function will return the previous working day for n = 1.</p><p>Calculate the date of the nth last working day of this month:</p><span style="color: rgb(0, 153, 0);font-family:courier new;" >=<span style="font-weight: bold;">FirstWorkDate</span>(MonthEnd(Today()), n)</span><br /><br /><span style="font-style: italic;">eg</span> <span style="font-weight: bold;">FirstWorkDate</span>(MonthEnd('2010/09/15'), 2) = '2010/09/29', so the 2nd last working day of September is the 29th.<br /><h2 style="font-family: trebuchet ms; color: rgb(102, 102, 204);">Long Form</h2>Both these functions have an optional long form similar to NetWorkDays, namely a list of non-working days to take into account in the calculation. See the <a href="http://yahqblog.blogspot.com/2010/09/qlikview-working-day-functions.html">earlier post on NetWorkDays</a> for more information.<br /><br />See the next post on <a href="http://yahqblog.blogspot.com/2010/10/qlikview-working-day-functions-3.html">putting it all together</a>.Jonathanhttp://www.blogger.com/profile/01169660018216478519noreply@blogger.com3tag:blogger.com,1999:blog-4667382907846182284.post-88411377123850322012010-09-16T21:31:00.010+02:002010-10-14T08:44:11.362+02:00Qlikview Working Day Functions 1<p>This post begins a short series on how to use the working day functions in Qlikview.</p> <h2 style="color: rgb(102, 102, 204);">NetWorkDays (short form)</h2>Calculate the working day number for today:<br /><br /><span style="color: rgb(0, 153, 0);font-size:85%;" >=<b>NetWorkDays</b>(MonthStart(Today()), Today())</span> <p><i>eg</i> <span style="font-size:85%;"><b style="font-family: courier new;">NetworkDays</b><span style="font-family:courier new;">(MonthStart('2010/09/15'), '2010/09/15') = 11</span></span>, so the 15<sup>th</sup> is the 11<sup>th</sup> working day of September 2010.</p> <p>Calculate the number of working days in the current month:</p><p style="color: rgb(0, 153, 0);font-family:courier new;"><span style="font-size:85%;">=<b>NetWorkDays</b>(MonthStart(Today()), MonthEnd(Today()))</span></p> The above examples are the short form of NetWorkDays, which consider working days to be Monday to Friday. <i><br /><br />One limitation for people living in regions with calendars different to the standard Western calendar is that there does not appear to be a way to make the basis for the working day calculations to be anything other than Monday to Friday.</i><br /><h2 style="color: rgb(102, 102, 204);">NetWorkDays (long form)</h2> <p>The long form of NetWorkDays allows you to take public holidays into consideration. This format adds an arbitrary number of dates to the parameter list which will be considered non-working days, such as:</p><p style="font-size: 85%; color: rgb(0, 153, 0); font-family: courier new;">=<b>NetWorkDays</b>(MonthStart(Today()), Today(), '2010/09/24', '2010/09/25')</p><p>This will treat the 24<sup>th</sup> and 27<sup>th</sup> of September 2010 as non-working days. Note that 25 September 2010 is a Saturday, so is already a non-working day. This is correctly ignored by the NetWorkDay() function.</p> <h2 style="color: rgb(102, 102, 204);">Using the long form</h2> <p>I have usually used the long form by reading public holidays from a spreadsheet (any data source will do), and concatenating the results into a variable. The script is:</p> <pre><span style="color: rgb(0, 153, 0);font-family:courier new;" ><br /><span style="font-style: italic; color: rgb(0, 153, 0);font-size:85%;" >tmpHoliday</span><span style="color: rgb(0, 153, 0);font-size:85%;" >:<br />LOAD Date([DATE], 'yyyy/MM/dd') as Date<br />FROM [..\QVDATA\Public Holidays.xlsx]<br />(ooxml, embedded labels, table is Sheet1);<br /><br /></span><span style="font-style: italic; color: rgb(0, 153, 0);font-size:85%;" >tmpConcat</span><span style="color: rgb(0, 153, 0);font-size:85%;" >:<br />LOAD concat(chr(39) & Date & chr(39),',') AS HolidayDates<br />RESIDENT tmpHoliday;<br /><br />Let vPublicHolidays = fieldvalue('HolidayDates',1);<br /><br />DROP TABLE tmpHoliday;<br />DROP TABLE tmpConcat;</span><br /></span></pre><br />Now I can use vPublicHolidays like this:<br /><p style="color: rgb(0, 153, 0);font-family:courier new;"><span style="font-size:85%;">=<b>NetWorkDays</b>(MonthStart(Today()), Today(), $(vPublicHolidays))</span></p><br /><p>Next article on <a href="http://yahqblog.blogspot.com/2010/09/qlikview-working-day-functions-2.html">FirstWorkDate and LastWorkDate</a><br /></p>Jonathanhttp://www.blogger.com/profile/01169660018216478519noreply@blogger.com5tag:blogger.com,1999:blog-4667382907846182284.post-52649779971709820392010-08-04T13:52:00.004+02:002010-08-04T13:59:33.396+02:00Searching QlikCommunityMany of you will know that the forums of <a href="http://community.qlikview.com/">QlikCommunity</a> are extemely helpful if you have a QV problem.<br /><br />Heres a useful tip that I picked up elsewhere:<br /><br />You can use google to search QlikCommunity. For example, enter this as the Google search string:<br /><br /><span style="color: rgb(51, 51, 255);font-family:courier new;" >Rolling 12 Months site:qlikview.com</span>Jonathanhttp://www.blogger.com/profile/01169660018216478519noreply@blogger.com0tag:blogger.com,1999:blog-4667382907846182284.post-46491685012828101112010-05-18T12:35:00.005+02:002010-05-18T12:54:42.884+02:00Copying coloursDid you know that you can copy colours from one colour block to another in QV?<br /><br /><div style="text-align: center;"><a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhUE8j7CsK2jTPfOXy8Px_T00aQoLOToe0LxiUkqcdoHvMqjJOsYF3nC7qOHb7iCx4qXON_ZmNCnxB7Y9FLDdcSa2dcxEfOsHRSceXVU6NHFgGD9t90wogtQmpHynqDct69Z7NQzZ61TbM/s1600/ColourBlock.PNG"><img style="display: block; margin: 0px auto 10px; text-align: center; cursor: pointer; width: 100px; height: 45px;" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhUE8j7CsK2jTPfOXy8Px_T00aQoLOToe0LxiUkqcdoHvMqjJOsYF3nC7qOHb7iCx4qXON_ZmNCnxB7Y9FLDdcSa2dcxEfOsHRSceXVU6NHFgGD9t90wogtQmpHynqDct69Z7NQzZ61TbM/s320/ColourBlock.PNG" alt="" id="BLOGGER_PHOTO_ID_5472557234229862274" border="0" /></a><span style="font-size:85%;"><span style="font-style: italic;">Qlikview colour block</span></span><br /></div><br />Right click on the colour block for a context menu with the following options:<br /><br /><ol><li><span style="font-weight: bold;">Copy </span>- copy the colour information to the clipboard. This information includes the colour gradient information.</li><li> <span style="font-weight: bold;">Paste All </span>- paste the complete colour information from the clipboard.<br /></li><li><span style="font-weight: bold;">Paste Base Color </span>- paste the base colour only.<br /></li><li><span style="font-weight: bold;">Paste Second Color</span> - paste the second colour (if any).<br /></li><li><span style="font-weight: bold;">Paste Gradients</span> - paste the gradient information (type and shading style).</li></ol>Jonathanhttp://www.blogger.com/profile/01169660018216478519noreply@blogger.com0tag:blogger.com,1999:blog-4667382907846182284.post-40615620324398447952010-04-28T21:38:00.003+02:002010-04-28T21:55:58.594+02:00Design ModeOne of the criticisms of Qlikview that I have heard raised is that there is no design mode - in other words the same environment is used to design models and to use them.<br /><br />However, QV <span style="font-style: italic;">does</span> have a design mode - of sorts. I didn't know this until recently and stmbled across this indirectly in another blog (sorry, can't remember whose).<br /><br />To activate design mode, turn on the design grid. On the menu click View | Design Grid or press Ctrl-G to toggle on and off.<br /><br />OK, so what do I get in this design mode, apart from the grid of course? Well, now I have sizing/moving handles on sheet objects, and some additional context menu items (z-ordering sheet objects, column width aids, interactive sorting and indivdual cell properties)<br /><br />I said "of sorts" above because it is not a pure design mode like other applications. You can still interact with the model as a normal user, mixed up with the design stuff.Jonathanhttp://www.blogger.com/profile/01169660018216478519noreply@blogger.com0tag:blogger.com,1999:blog-4667382907846182284.post-51143453459699527192010-04-21T13:34:00.003+02:002010-04-21T14:11:34.052+02:00Selection and month viewsIf you set up a simple month view chart such as a chart of the sum of transaction amounts by day, for example:<br /><ul><li>using a dimension such as Day(TransDate)</li><li>using an expression like Sum(TransAmount)</li></ul>Now, if the user views this chart with no year and month selected, they will get the sum of amounts for that day of the month for all months loaded into the model. Not very useful.<br /><br />Another approach is to make the chart display summed amounts for the current month. If a selection is made, then display summed amounts for the selected month/year.<br /><br />This is how I do this:<br /><ol><li>Create TransYear and TransMonth fields on loading</li><li>Put listboxes with these two fields on the sheet</li><li>Build the chart</li><li>Filter the chart expression(s) with the following date filter (added filter highlighted):</li><br /><span style="color: rgb(102, 102, 102);">Sum(</span><span style="font-style: italic; color: rgb(0, 0, 153);">If(TransDate >= MonthStart(Max(Total TransDate)) AND TransDate <= MonthEnd(Max(Total TransDate)), </span><span style="color: rgb(102, 102, 102);">TransAmount)</span><br /><br /><li>Now, if nothing is selected, the TransDate range will be in the current month (or at least the last month in which a transaction was recorded - in most cases this will be the same date).</li></ol>If you select a month only, then Max(TransDate) will be the month in the most recent year that contains data for that month. For example, in April: selecting Jan, Feb, Mar or Apr will display data for that month in the current year. Selecting any other month would display data for that month in the previous year.<br /><br />If you select a year only, then Max(TransDate) will be the last day of that year with transactions. If that is the current year, then that will be today (or yesterday).<br /><br />Any other selection (of TransDate, TransMonth, TransYear) will cause the month with of the last possible TransDate to be displayed.<br /><br /><span style="font-size:85%;">You can, of course, do the filtering with a set expression if you prefer. I find this syntax more intuitive, but the final choice should depend on performance. Maybe you would like to convert this to a set expression in a comment below?<br /></span>Jonathanhttp://www.blogger.com/profile/01169660018216478519noreply@blogger.com0tag:blogger.com,1999:blog-4667382907846182284.post-31656627352130959282010-04-13T11:29:00.006+02:002010-04-21T14:12:57.497+02:00Tooltip for buttonsDid you know that QV models can display a <a href="http://en.wikipedia.org/wiki/Tooltip">tooltip</a> for buttons?<br /><br />Enter the tooltip text in the Help Text box on the Caption tab of the button properties. If the user hovers their mouse over the button, the text will pop up in tooltip.<br /><br />This also works for text boxes.Jonathanhttp://www.blogger.com/profile/01169660018216478519noreply@blogger.com0tag:blogger.com,1999:blog-4667382907846182284.post-56635046955702667782010-04-13T11:26:00.004+02:002010-04-21T14:13:48.634+02:00Set Analysis Problem<strong>The problem</strong><br /><br />To display data in a way that ignores selections, you can use a set expression {1} in any aggregation functions. This is useful for a dashboard page which displays (for example) the last 30 days data. The chart expression could be something like:<br /><span style="color: rgb(0, 0, 153);">=Sum({1} Distinct If(</span><em style="color: rgb(0, 0, 153);">TranDate</em><span style="color: rgb(0, 0, 153);">>=today()-30, </span><em style="color: rgb(0, 0, 153);">TranValue</em><span style="color: rgb(0, 0, 153);">)</span>)<br /><br />I have a chart to display data which must ignore all selections, except one (on a field named <em>ForcedLoadingState</em>, which could contain 'Yes' or 'No'). The problem was how to create a set expression which would ignore all selections except on <em>ForcedLoadingState</em>.<br /><br /><strong>The solution</strong><br /><br />1. Create a variable (eg <em>vForcedLoadingState</em>).<br /><br />2. Use the set expression:<br /><span style="color: rgb(0, 0, 153);">{1<</span><em style="color: rgb(0, 0, 153);">ForcedLoadingState</em><span style="color: rgb(0, 0, 153);">={$(=</span><em style="color: rgb(0, 0, 153);">vForcedLoadingState</em><span style="color: rgb(0, 0, 153);">)}>} </span><br /><br />3. Define the variable as:<br /><span style="color: rgb(0, 0, 153);">=If(</span><strong style="color: rgb(0, 0, 153);">Len</strong><span style="color: rgb(0, 0, 153);">(</span><em style="color: rgb(0, 0, 153);">ForcedLoadingState</em><span style="color: rgb(0, 0, 153);">)=0,'Yes,No',</span><em style="color: rgb(0, 0, 153);">ForcedLoadingState</em><span style="color: rgb(0, 0, 153);">) </span><br /><br /><strong>Comments</strong><br /><br />This allows the user to see the data unfiltered by <em>ForcedLoadingState</em> (ie no selection) or for either state of <em>ForcedLoadingState</em>. When <em>ForcedLoadingState</em> is unfiltered, the variable will pass both Yes and No as valid options to the set expression. The '1' in the set expression overrides all other selections.<br /><br />If(Len(field) = 0,..) is equivalent to If(IsNull(field),..), but I have found the Len approach more reliable. Maybe that's just me :-)<br /><br />If there are more than two valid states, then the variable expression will need to include all the options in a comma separated list. This will work as long as there is a small number of possible options.Jonathanhttp://www.blogger.com/profile/01169660018216478519noreply@blogger.com0tag:blogger.com,1999:blog-4667382907846182284.post-48326506916263932542010-04-13T11:00:00.005+02:002010-04-21T14:15:17.072+02:00Qlikview Log ViewerI have developed a simple Qlikview model that reads the log files for the Qlikview models in the same folder and shows a simple status dashboard showing the last reload date, success or failure of the reload, the number of reload errors if any and the relative time that the reload took. See image below:<br /><br /><a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhVrKbyEmZ3lAd1A-83jLkvLVxidjUIMiK9O3g-QYLrFubjFN4kyBI4kEvCexNgR99LiuRRU8u12adZ_qQiaQrFAeI6yH765sKD_xm3O69ZVsFS52lbRZ18kVJChxgizZpV-xp8mYAwG58/s1600-h/QV+Log+Viewer+image.png"><img id="BLOGGER_PHOTO_ID_5448868094610401730" style="display: block; margin: 0px auto 10px; width: 320px; cursor: pointer; height: 128px; text-align: center;" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhVrKbyEmZ3lAd1A-83jLkvLVxidjUIMiK9O3g-QYLrFubjFN4kyBI4kEvCexNgR99LiuRRU8u12adZ_qQiaQrFAeI6yH765sKD_xm3O69ZVsFS52lbRZ18kVJChxgizZpV-xp8mYAwG58/s320/QV+Log+Viewer+image.png" border="0" /></a><br />To use, just copy the qvw file into the same folder and enable logging on the models that you wish to monitor, and set this model to reload periodically. If you have a series of models reloading overnight, just ensure that this model runs last.<br /><br />Clicking on one of the model names displays the log data for the last reload of that model.<br /><br />The load script is:<br /><code><br /><span style="font-size:85%;"><span style="font-family:courier new;">S</span><span style="color: rgb(51, 51, 153);font-family:courier new;" >ET ThousandSep=' ';</span><br /><span style="color: rgb(51, 51, 153);font-family:courier new;" >SET DecimalSep='.';</span><br /><span style="color: rgb(51, 51, 153);font-family:courier new;" >SET MoneyThousandSep=' ';</span><br /><span style="color: rgb(51, 51, 153);font-family:courier new;" >SET MoneyDecimalSep='.';</span><br /><span style="color: rgb(51, 51, 153);font-family:courier new;" >SET MoneyFormat='R # ##0.00;R-# ##0.00';</span><br /><span style="color: rgb(51, 51, 153);font-family:courier new;" >SET TimeFormat='hh:mm:ss';</span><br /><span style="color: rgb(51, 51, 153);font-family:courier new;" >SET DateFormat='YYYY/MM/DD';</span><br /><span style="color: rgb(51, 51, 153);font-family:courier new;" >SET TimestampFormat='YYYY/MM/DD hh:mm:ss';</span><br /><span style="color: rgb(51, 51, 153);font-family:courier new;" >SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';</span><br /><span style="color: rgb(51, 51, 153);font-family:courier new;" >SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';</span><br /><br /><span style="color: rgb(51, 51, 153);font-family:courier new;" >For each File in filelist ('*.log')</span><br /><br /><span style="color: rgb(51, 51, 153);font-family:courier new;" >tmpLOG:</span><br /><span style="color: rgb(51, 51, 153);font-family:courier new;" >REPLACE LOAD</span><br /><span style="color: rgb(51, 51, 153);font-family:courier new;" >Date#(@1:9, 'MM/dd/yyyy') as Date, </span><br /><span style="color: rgb(51, 51, 153);font-family:courier new;" >Time#(@10:17, 'hh:mm:ss') as Time, </span><br /><span style="color: rgb(51, 51, 153);font-family:courier new;" >@20:24 as SourceLine,</span><br /><span style="color: rgb(51, 51, 153);font-family:courier new;" >RecNo() AS LogLine, </span><br /><span style="color: rgb(51, 51, 153);font-family:courier new;" >PurgeChar(Trim(@25:n), chr(9)) as Detail,</span><br /><span style="color: rgb(51, 51, 153);font-family:courier new;" >FileName() AS LogFile,</span><br /><span style="color: rgb(51, 51, 153);font-family:courier new;" >FileTime('$(File)') AS LogDate </span><br /><span style="color: rgb(51, 51, 153);font-family:courier new;" >FROM '$(File)'</span><br /><span style="color: rgb(51, 51, 153);font-family:courier new;" >(fix, codepage is 1252);</span><br /><span style="color: rgb(51, 51, 153);font-family:courier new;" ></span><br /><span style="color: rgb(51, 51, 153);font-family:courier new;" >Let zStartts = TimeStamp#(Date(Peek('Date', 0), 'yyyy/MM/dd') & ' ' & Peek('Time', 0), TimestampFormat);</span><br /><span style="color: rgb(51, 51, 153);font-family:courier new;" >Let zEndts = TimeStamp#(Date(Peek('Date', -1), 'yyyy/MM/dd') & ' ' & Peek('Time', -1), TimestampFormat); </span><br /><span style="color: rgb(51, 51, 153);font-family:courier new;" >Let zInterval = Interval(zEndts - zStartts, 'hh:mm:ss');</span><br /><span style="color: rgb(51, 51, 153);font-family:courier new;" ></span><br /><span style="color: rgb(51, 51, 153);font-family:courier new;" >Let zFileName = Peek('LogFile');</span><br /><br /><span style="color: rgb(51, 51, 153);font-family:courier new;" ><span style="font-style: italic;">LOG</span>:</span><br /><span style="color: rgb(51, 51, 153);font-family:courier new;" >LOAD *,</span><br /><span style="color: rgb(51, 51, 153);font-family:courier new;" >'$(zInterval)' AS ElapsedTime</span><br /><span style="color: rgb(51, 51, 153);font-family:courier new;" >RESIDENT tmpLOG</span><br /><span style="color: rgb(51, 51, 153);font-family:courier new;" >WHERE Len(Date) > 0 AND Len(Detail) > 0;</span><br /><span style="color: rgb(51, 51, 153);font-family:courier new;" ></span><br /><span style="color: rgb(51, 51, 153);font-family:courier new;" ></span><br /><span style="color: rgb(51, 51, 153);font-family:courier new;" ><span style="font-style: italic;">ERRORLOG</span>:</span><br /><span style="color: rgb(51, 51, 153);font-family:courier new;" >LOAD</span><br /><span style="color: rgb(51, 51, 153);font-family:courier new;" >LogFile,</span><br /><span style="color: rgb(51, 51, 153);font-family:courier new;" >LogDate,</span><br /><span style="color: rgb(51, 51, 153);font-family:courier new;" >Count(*) AS ErrorCount</span><br /><span style="color: rgb(51, 51, 153);font-family:courier new;" >RESIDENT tmpLOG</span><br /><span style="color: rgb(51, 51, 153);font-family:courier new;" >WHERE Detail LIKE 'Error:*'</span><br /><span style="color: rgb(51, 51, 153);font-family:courier new;" >GROUP BY LogFile, LogDate; </span><br /><span style="color: rgb(51, 51, 153);font-family:courier new;" ></span><br /><span style="color: rgb(51, 51, 153);font-family:courier new;" >DROP TABLE tmpLOG;</span><br /><br /><span style="color: rgb(51, 51, 153);font-family:courier new;" >Next File</span><br /></span></code><br />You may need to tweak the data formats to suit your system.<br /><br />Load the model <a href="http://docs.google.com/leaf?id=0BzFdSk9UQtT8ZmJmMDk1MDItOTQ1OC00NGI0LTkwOTktNWRlMzQ3ZDRkMGZk&hl=en_GB">here</a>. You will require a licensed version of Qlikview to open this model. If you are using the community download version of QV, you can roll your own by creating a new model, copying and pasting the script above, and importing <a href="http://docs.google.com/leaf?id=0BzFdSk9UQtT8NzhlZTUwYWUtMGM3YS00MGUyLWFlOWQtYmFiMGMwYTdmOTZk&hl=en_GB">this XML sheet layout</a>.<br /><br /><span style="font-size:85%;"><span style="font-style: italic;">The standard free software disclaimers apply.</span></span>Jonathanhttp://www.blogger.com/profile/01169660018216478519noreply@blogger.com0