Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator III
Creator III

Add rows for days in between

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:

IDDateValue
11/1/2013A
11/5/2013B
11/10/2013C
21/2/2013A
21/4/2013B
21/6/2013C

Desired Result:

IDDateValue
11/1/2013A
11/2/2013A
11/3/2013A
11/4/2013A
11/5/2013B
11/6/2013B
11/7/2013B
11/8/2013B
11/9/2013B
11/10/2013C
21/2/2013A
21/3/2013A
21/4/2013B
21/5/2013B
21/6/2013C
1 Solution

Accepted Solutions
Highlighted
Creator III
Creator III

Re: Add rows for days in between

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;


View solution in original post

6 Replies
Highlighted
Not applicable

Re: Add rows for days in between

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;



Highlighted
Specialist II
Specialist II

Re: Add rows for days in between

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

Highlighted
Creator III
Creator III

Re: Add rows for days in between

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;


View solution in original post

Highlighted
MVP & Luminary
MVP & Luminary

Re: Add rows for days in between

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;


talk is cheap, supply exceeds demand
Highlighted
Creator III
Creator III

Re: Add rows for days in between

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.

Highlighted
Not applicable

Re: Add rows for days in between

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.