topic Reading latest 4 files based on period in QlikView

Reading latest 4 files based on period

Wed, 28 Jul 2010 06:39:35 GMT

Hi,

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.

Reading latest 4 files based on period

manishkumar75 — Wed, 28 Jul 2010 07:17:57 GMT

Hi,

I am not sure , but you can try for

=Date(addmonths(MonthStart(Max([Your Date])),-1),'MMYYYY') ,

Date(addmonths(MonthStart(Max([Your Date])),-2),'MMYYYY') and so on.

Regards,

Manish Kumar

Reading latest 4 files based on period

Anonymous — Wed, 28 Jul 2010 07:24:36 GMT

Hey there,

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;

Drop table T1;

Reading latest 4 files based on period

Thu, 29 Jul 2010 08:36:08 GMT

Thanks Johannes. It worked and I am able to get the last 4 files now.

I want to read them now one by one in single table. I tried with for loop but as Name is now in the LastFour table, I am not able to have it in loop.

Any suggestion for that?

Reading latest 4 files based on period

Anonymous — Thu, 29 Jul 2010 08:54:09 GMT

Yeah,

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.

Reading latest 4 files based on period

Thu, 29 Jul 2010 09:25:27 GMT

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.