Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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.
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.