Create rows in a script

Tue, 10 Sep 2013 14:41:23 GMT

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

Re: Create rows in a script

Clever_Anjos — Tue, 10 Sep 2013 14:46:00 GMT

Concatenate a record to your Temp2 table before loading result

concatenate(Temp2)

LOAD

today() as NextDate

autogenerate 1;

Re: Create rows in a script

hic — Wed, 11 Sep 2013 02:09:06 GMT

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

topic Re: Create rows in a script in QlikView

Create rows in a script

Re: Create rows in a script

Re: Create rows in a script