I am working on two scripts right now - (a) first one creates the QVD files (b) Second one reads the QVDs and creates a model for dashboard.
The dashboard should consider only last 4 months data. The issue here is the last 4 months can be like 201006, 201003, 201002, 201001. When creating in (a) I am using input fields to decide the period for which QVDs have to be created and then create QVDs for the period.
Let us say now I have now the QVDs for above months. When I run (a) in Aug 10, I would create files for 201007. The files available now are for 5 months. Then (b) should read only last 4 months i.e. 201007, 201006, 201003 and 201002.
I want achive this when I run (b) and last 4 months should be picked automatically based on available period QVD files.
I read something about Edit Module which allows one to do the VB Script. But as I have just started thought to check if this is the best way or some other options are also available.
No need for macros. Easier to do this in the QlikView script. Here's an example I threw together. In your case it's easy since the more recent the QVD file, the larger the numerical value representing it. 201007 is larger than 201006 which is larger than 201003, so basically you want to pick the 4 largest QVD "numbers".
In this example I created a QVDFolder on C: and created 5 QVDs, 201007, 201006, 201005, 201003 & 201002.
The following script picks up all QVD-files from the folder in the top variable. Then it loads then in descending order so that 201007 ends up on top and loads the first 4 occurences, i.e. the first 4 month QVDs:
Let vQVD_Dir = 'C:\QVDFolder\';
for each Ext in 'qvd'
for each File in filelist ('$(vQVD_Dir)' & '*.' &Ext) T1: Load distinct filename() as Name from '$(File)';
next File
next Ext
NOCONCATENATE LastFour: First 4 Load Name resident T1 Order by Name desc;
No need for macros. Easier to do this in the QlikView script. Here's an example I threw together. In your case it's easy since the more recent the QVD file, the larger the numerical value representing it. 201007 is larger than 201006 which is larger than 201003, so basically you want to pick the 4 largest QVD "numbers".
In this example I created a QVDFolder on C: and created 5 QVDs, 201007, 201006, 201005, 201003 & 201002.
The following script picks up all QVD-files from the folder in the top variable. Then it loads then in descending order so that 201007 ends up on top and loads the first 4 occurences, i.e. the first 4 month QVDs:
Let vQVD_Dir = 'C:\QVDFolder\';
for each Ext in 'qvd'
for each File in filelist ('$(vQVD_Dir)' & '*.' &Ext) T1: Load distinct filename() as Name from '$(File)';
next File
next Ext
NOCONCATENATE LastFour: First 4 Load Name resident T1 Order by Name desc;
Just apply the same principle and step through the 4 values in the Name field like this:
For a=0 to FieldValueCount('Name')-1 Let vFilename = Peek('Name',$(a),'LastFour'); Load * from '$(vFilename)' (qvd); next
First I initiate a loop from 0-3, (FieldValueCount checks the number of values in the field Name). Then I pull the actual value into a variable, using Peek(). When using Peek, the first row is 0, hence the loop from 0-3 instead of 1-4. Then I use the variable, vFilename in the from statement in the QVD load. Hope it makes sense.
Thanks a lot Johannes! It worked now. I was able to create working model from single period files. The only issue was making it to work automatically with last 4 periods. I have one QVD for each table from source for each period.
The major hurdle (according to me) was having the 4 part automated.
