load only last 12 weeks from the QVD using where clause in load script

RheaQv12 — Sat, 18 Jan 2020 01:45:40 GMT

Hi ,

I am trying to load only last 13weeks of data from the QVD which includes lot of data but while loading into the dashboard - I only need to get the last 13 weeks data using the max date value in the field created_date.

From the field Created_Date I have to get the weekend dates using weekend(Created_Date,0,6) - this is what we use to get the weekend date

I have the script something below

//load max weekend date into a temp table and put this into variable

Temp:

Load

MAX(WeekEnd(Created_Date,0,6) as MAX_WeekEndDate

From QVD;

let vMaxWeekEndDate=peek(get the value into the variable);

Test:

Load

Created_Date, // date format

attribute1,

attribute2,

attribute3,

metric1,

metric2

from QVD

WHERE WeekEnd(Created_Date,0,6) >='vMaxWeekEndDate' //say this returns the 01/18/2020

and WeekEnd(Created_Date,0,6) <= //this is where I got stuck - how do I get the last 13 weekends date here

here last 13th weekend date should be - this should be something in October 2019

could anyone please help - I really appreciate your time - please let me know if this is not clear

thanks a lot

Re: load only last 12 weeks from the QVD using where clause in load script

Kushal_Chawda — Sat, 18 Jan 2020 12:19:15 GMT

Temp: Load date(WeekEnd(max(Created_Date),0,6)) as MAX_WeekEndDate From QVD; let vMaxWeekEndDate=peek('MAX_WeekEndDate',0,'Temp');

Now you can use this Max date variable to generate Temp dates till last five month to check the Last 13 week Dates using Inweek function

Last13Week: Load date(weekend(max(Dates),0,6)) as Last13WeekDate where Last13weekFlag=1; Load *,InWeek(Dates,'$(vMaxWeekEndDate)',-13)*-1 as Last13weekFlag; Load date(addmonths('$(vMaxWeekEndDate)',-5)+ IterNo()-1) as Dates AutoGenerate 1 While addmonths('$(vMaxWeekEndDate)',-5)+ IterNo()-1<='$(vMaxWeekEndDate)'; Let vLast13WeekDate = Peek('Last13WeekDate',0,'Last13Week');

Now you can use these two variable in where condition while load data from QVD

Test: Load Created_Date, // date format attribute1, attribute2, attribute3, metric1, metric2 from QVD WHERE Created_Date >='$(vLast13WeekDate)' and Created_Date <= '$(vMaxWeekEndDate)';

If your Date field format in QVD is not in actual Date format then use something like below. Otherwise above where condition should work.

WHERE date(date#(Created_Date,'YYYY/MM/DD')) >='$(vLast13WeekDate)' and date(date#(Created_Date,'YYYY/MM/DD')) <= '$(vMaxWeekEndDate)'; Note : Date format given in Date# should match with format of Date field in QVD

topic Re: load only last 12 weeks from the QVD using where clause in load script in QlikView

load only last 12 weeks from the QVD using where clause in load script

Re: load only last 12 weeks from the QVD using where clause in load script