topic Re: Add rows for days in between in QlikView

Add rows for days in between

Anonymous — Tue, 25 Jun 2013 15:04:20 GMT

I have a situation where I need to add rows to a table to fill in the missing dates between changes in a value. I am confused on Do While, Interval Match, For loops and/or a combination of all. Can someone straighten me out?

Data:

ID	Date	Value
1	1/1/2013	A
1	1/5/2013	B
1	1/10/2013	C
2	1/2/2013	A
2	1/4/2013	B
2	1/6/2013	C

Desired Result:

ID	Date	Value
1	1/1/2013	A
1	1/2/2013	A
1	1/3/2013	A
1	1/4/2013	A
1	1/5/2013	B
1	1/6/2013	B
1	1/7/2013	B
1	1/8/2013	B
1	1/9/2013	B
1	1/10/2013	C
2	1/2/2013	A
2	1/3/2013	A
2	1/4/2013	B
2	1/5/2013	B
2	1/6/2013	C

Re: Add rows for days in between

Tue, 25 Jun 2013 15:46:33 GMT

let vmindate= num(date#('01-01-2013'));

let vmaxdate=num(date#('10-01-2013'));

test1:

load * inline

[ID, TempDate, Value

1, 1/1/2013, A

1, 1/5/2013, B

1, 1/10/2013,C

2, 1/2/2013, A

2, 1/4/2013, B

2, 1/6/2013, C ];

test:

Load Date($(vmindate)+rowno()-1,'M/D/YYYY') as TempDate

AutoGenerate $(vmaxdate)-$(vmindate)+1;

left join

test2:

load Date#(TempDate,'M/D/YYYY') as TempDate ,ID,Value resident test1;

test3:

load TempDate as t ,if(isnull(ID),previous(ID),ID) as i,if(isnull(Value),previous(Value),Value) as v resident test;

drop table test1,test;

Re: Add rows for days in between

nizamsha — Tue, 25 Jun 2013 15:55:59 GMT

http://community.qlik.com/docs/DOC-3786 this link will be very useful for wht ur searching

Re: Add rows for days in between

Anonymous — Tue, 25 Jun 2013 18:52:14 GMT

Thanks to kalraja4 and nizamsha for their helpful answers. Here is the final version that worked for this exercise:

TempData:
LOAD * INLINE
[ID, Date, Value
1, 1/1/2013, A
1, 1/5/2013, B
1, 1/10/2013,C
2, 1/2/2013, A
2, 1/4/2013, B
2, 1/6/2013, C ];

//   Create a list of the id and the date range for that id
ID_List:
LOAD ID,
MIN(Date) AS MinDate,
MAX(Date) AS MaxDate
RESIDENT TempData
GROUP BY ID;

//   Assign a variable that tells us how many loops to complete
LET vRowCount = NoOfRows('ID_List');

//   Set a variable to help us generate the correct date range for the id we are on
SET vRowCursor = 0;

//   create a loop to cycle through each id and generate the missing dates
FOR i = 1 TO $(vRowCount)

//   assign variables for each loop iteration
     LET vID = PEEK('ID',$(i)-1,'ID_List');                    //   id we are currently on
     LET vMinDate = PEEK('MinDate',$(i)-1,'ID_List');     //   min date for that id
     LET vMaxDate = PEEK('MaxDate',$(i)-1,'ID_List');     //   max date for that id

//   generate the date range for the id we are on.
     DatePop:
Load $(vID) AS ID, DATE($(vMinDate)+ROWNO()-$(vRowCursor)-1,'M/D/YYYY') as Date
AutoGenerate $(vMaxDate)-$(vMinDate)+1;

//   count how many rows are in the table so our date autogenerate creates the correct range
     LET vRowCursor = NoOfRows('DatePop');

NEXT i

//   we don't need this temporary table anymore.
DROP TABLE ID_List;

//   join the newly created dates to my original table
JOIN (TempData)
LOAD *
RESIDENT DatePop;

//   create a new table filling in the value for the new dates based on the prior value
Data:
NOCONCATENATE
LOAD ID, Date, IF(ISNULL(Value),PEEK(Value),Value) AS Value    //   peek must be used rather than previous here because peek uses the newly created field values
RESIDENT TempData
ORDER BY ID, Date;   //   the order here is important.

//   these were temporary tables and are no longer needed.
DROP TABLE TempData, DatePop;

Re: Add rows for days in between

Gysbert_Wassenaar — Tue, 25 Jun 2013 19:16:26 GMT

Try this instead next time:

TempData:

LOAD ID, Date#(Date,'M/D/YYYY') as Date, Value INLINE

[ID, Date, Value

1, 1/1/2013, A

1, 1/5/2013, B

1, 1/10/2013,C

2, 1/2/2013, A

2, 1/4/2013, B

2, 1/6/2013, C ];

Temp2:

load *, if(previous(ID)=ID, previous(Date),Date+1) as NextDate

Resident TempData

order by ID, Date desc;

drop table TempData;

Result:

load ID, date(Date + IterNo() -1) as Date, Value

Resident Temp2

while Date + IterNo() - 1 < NextDate

order by ID, Date;

drop table Temp2;

Re: Add rows for days in between

Anonymous — Tue, 25 Jun 2013 22:44:56 GMT

Gysbert,

That looks to be a much more efficient solution. Many ways to skin a cat. Some ways are better than others. Thanks for the solution.

Re: Add rows for days in between

Thu, 20 Oct 2016 17:10:40 GMT

gwassenaar‌‌,

Elegant solution

What would you recommend altering if I would like to modify the script so that from the last record it continues to fill in the blanks with the last value until todays date? I tried some alterations to it but could not get the desired result.