Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi There
I am currently using the script below to populate dates between a slowly changing dimension to determine whether a container is hired out or available.
Temp2:
load *, if(previous(Unit)=Unit, previous([Movement Date]),[Movement Date]+1) as NextDate
Resident Movement
order by Unit, [Movement Date] desc;
drop table Movement;
Result:
load Unit, date([Movement Date] + IterNo() -1) as [Movement Date], [Available/On-Hire]
Resident Temp2
while [Movement Date] + IterNo() - 1 < NextDate
order by Unit, [Movement Date];
This has successfully created a row for each date between the first movement date and the last movement date. However I now need to create rows between the last movement date up until today's date.
If anybody has any ideas or advice on how to do this then it would be much appreciated.
Kind Regards,
Steven
Concatenate a record to your Temp2 table before loading result
concatenate(Temp2)
LOAD
today() as NextDate
autogenerate 1;
I would use a join and a peek() as described in http://community.qlik.com/blogs/qlikviewdesignblog/2013/02/05/populating-a-sparsely-populated-field
HIC