Discussion Board for collaboration related to QlikView App Development.
If I have startDate and endDate in my data, how can I create weeks starting from the startDate field and not the start of the year. So, basically I want to populate weeks between the two dates.
May be this?
Load *, Week(Date) as Week;
LOAD Date(MinDate+IterNo()-1) as Date
While MinDate+IterNo()-1 <= MaxDate;
LOAD Min(startDate) as MinDate,
Max(endDate) as MaxDate
I would suggest the method from Anil Babu. If you want another method you could use a Cartesion join:
LEFT JOIN (ExampleTable) LOAD
RESIDENT [Master Calendar];
WEEK(Date) AS Week
WHERE DATE(Date ) >= DATE([StartDate]) AND DATE(Date) <= DATE([EndDate])
DROP TABLE ExampleTable;
Just wondering if I have to use resident load instead of autogenerate?
May be this?
Load * From Table;
LOAD Week(Final_Date) As Week;
Load Date(MinDate + IterNo() -1 ) AS Final_Date While Date(MinDate + IterNo() - 1) <= MaxDate;
Min(startDate) AS MinDate,
Max(endDate) AS MaxDate
By resident load you can try this way
let startDate = Num(MakeDate(2015,1,1));
let endDate = Num(MakeDate(2017,12,30));
Date($(startDate) + IterNo() -1) as Date
While Date($(startDate) + IterNo() -1) <= $(endDate);
Week(Date) as Week
DROP Table tmpCalendar;
Thanks for this, however my challenge is to create week intervals based off of a period of 84 days. So, essentially my data will only have week 1 to week 12 given the startDate and endDate.
is it possible to replace the hard coded dates in the variables with my start and end date columns?
Yes it is possible but keep the name same and check the variable values also will it return [properly or not.
Okay, Can you make sample and explain exact result set you want to see?