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

[RheaQv12]

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

[Kushal_Chawda · Accepted solution]

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