topic Re: Split dataset into seperate files based on month in QlikView

Split dataset into seperate files based on month

Anonymous — Thu, 27 Aug 2015 07:16:05 GMT

Hey everyone, brand new to Qlikview and this is a bit tricky to explain so please bear with me.

So we have monthly QVD files eg. 201506.qvd (June) ; 201507.qvd (July) ; 201508.qvd and so on containing data pulled from another system and turned into a QVD.

Data example is

Date	Name	Sales
20150730	Frank	3
20150730	Bob	5
20150801	Bob	2
20150801	Jenny	9

The script brings all data from these files to compile a year's worth of data up to a moving variable date 7 days ago. The script then brings in the most recent 7 days of data from the other system source, and appends it to the data from the QVDs. So the QVDs will always be up to the current date, but every day we effectively scrap the last 6 days data and overwrite them with the new 7 day data pull.

This part is working and all good when I use a single QVD file, but I've no idea where to start with the next step.

I need to then re-split out the data from this new table into monthly files, overwriting the files that exist.

In the most recent case, as we're looking back 7 days, it pulls in June to August's QVD data, appends the new, then :

201506.qvd would be overwritten with June data but identical

201507.qvd would be overwritten with July data but identical

201508.qvd would be overwritten with August data, however this file now contains 1 new day of data plus different data for the last 6 days.

I need this to occur automatically. The 7 day new data pull works off a Today()-7:Today() variable so there is ideally no manual input required. The bigger issue is when the 7 days crosses months (eg 29/06 - 06/08) as in this case 2 files would need to be overwritten with new data.

I'm open to suggestions on best way as I don't really even know where to start. If the best option is to only overwrite files where there are data changes then I'm fine with that.

Let me know if any of that needs clarification.

Thanks again,

Dave

Re: Split dataset into seperate files based on month

kuczynska — Thu, 27 Aug 2015 09:19:05 GMT

Just a quick thought - did you try to change your variable calculation from Today()-7; Today(), to something more complex, that will include the month change check within that time and will allow you to keep the previous month file unchanged, my thought here would be:

if month(Today() - 7) = month(Today()) -> do Your Previous Calculation,

else -> do your Previous calculation, but take dates monthstart(Today()) till Today()

But you will have to give us some sample data in order to check the above idea - not sure at this point how your variable is being calculated etc.

Re: Split dataset into seperate files based on month

jonathandienst — Thu, 27 Aug 2015 10:16:42 GMT

I trust this example code will help:

//---- Set parameters and variables

Let vPath = ....; // file path to qvd files

Let vToday = Num(Today()); // numeric

Let vBase = vToday - 7; // numeric

Let vMonthStart = Num(MonthStart(vToday));

Let vMonthEnd = Floor(MonthEnd(vBase));

Let vBaseName = Date(vBase, 'YYYYMM') & .'qvd';

Let vTodayName = Date(vToday, 'YYYYMM') & .'qvd';

//---- Update prior month qvd if necessary ----

If Month(vBase) <> Month(vToday) Then

T1:

LOAD *

FROM source

Where tDate >= $(vBase) And tDate <= $(vMonthEnd);

Concatenate (T1)

LOAD *

FROM [$(vPath)\$(vBaseName)] (qvd)

Where tDate < $(vBase) ;

STORE T1 into [$(vPath)\$(vBaseName)] (qvd);

DROP Table T1;

End If

//---- Update current month qvd

T1:

LOAD *

FROM source

Where tDate >= $(vMonthStart) And tDate <= $(vToday);

STORE T1 into [$(vPath)\$(vTodayName)] (qvd);

DROP Table T1;

//--- Now load the data from qvd ----

Main:

LOAD *

FROM [$(vPath)\*.qvd] (qvd);

Note that your will need to change the Load * from source to the correct load from your source system and the references to tDate to a Date field in your source (not a date/time field!).

If you want to apply some rules to the qvd's you load in the last step, change that From *.qvd to a For Each vFile in FileList('$(vPath)\*.qvd') loop, and parse the file name in vFile to determine if that file should be loaded.

Re: Split dataset into seperate files based on month

Anonymous — Thu, 27 Aug 2015 23:38:38 GMT

Sensational, that fills the current need so I'll give this a go today and post back if I get issues. the If statement is obviously the key here.

So an interim question - if the variable was larger, eg. today()-90 days and the new data passed over 3 (or more) months, - rather than using nested if statements - is there a way to create a script that looks within, pulls out all the various months in the data that have changed, and exports accordingly? This would work no matter if the new data crossed 2 months or 12.

This is the end-state goal of my current project.