Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have following problem:
I have a table like:
Date | ID | Cumulative |
---|---|---|
01.01.2014 | 1 | 1 |
03.01.2014 | 1 | 4 |
05.01.2014 | 2 | 1 |
07.01.2014 | 2 | 3 |
What I´m trying to achieve is, to generate all missing Dates for the IDs and fill the Rows with the "ID" and "Cumulative" from the previous "Cumulative" from the ID
So, result should be:
Date | ID | Cumulative |
---|---|---|
01.01.2014 | 1 | 1 |
02.01.2014 | 1 | 4 |
03.01.2014 | 1 | 4 |
04.01.2014 | 1 | 4 |
05.01.2014 | 1 | 4 |
06.01.2014 | 1 | 4 |
07.01.2014 | 1 | 4 |
01.01.2014 | 2 | 0 |
02.01.2014 | 2 | 0 |
03.01.2014 | 2 | 0 |
04.01.2014 | 2 | 0 |
05.01.2014 | 2 | 1 |
06.01.2014 | 2 | 1 |
07.01.2014 | 2 | 3 |
Thank you for any help
You should be able to do it like
INPUT:
LOAD Date,
ID,
Cumulative
FROM
[https://community.qlik.com/thread/188863]
(html, codepage is 1252, embedded labels, table is @1);
EXT:
LOAD Min(Date) as Min, Max(Date) as Max
RESIDENT INPUT;
Let vMin = Num(Peek('Min',0,'EXT'));
Let vMax = Num(Peek('Max',0,'EXT'));
RANGE:
LOAD DISTINCT ID Resident INPUT;
JOIN (RANGE)
LOAD Date($(vMin)+Recno()-1) as Date
AutoGenerate $(vMax)-$(vMin)+1;
LEFT JOIN (RANGE)
LOAD * Resident INPUT;
RESULT:
NOCONCATENATE
LOAD ID, Date, Rangesum(if(isNull(Cumulative) and Peek(ID) = ID, Peek(Cumulative), Cumulative)) as Cumulative
Resident RANGE
ORDER BY ID, Date;
DROP TABLE EXT, RANGE, INPUT;
You should be able to do it like
INPUT:
LOAD Date,
ID,
Cumulative
FROM
[https://community.qlik.com/thread/188863]
(html, codepage is 1252, embedded labels, table is @1);
EXT:
LOAD Min(Date) as Min, Max(Date) as Max
RESIDENT INPUT;
Let vMin = Num(Peek('Min',0,'EXT'));
Let vMax = Num(Peek('Max',0,'EXT'));
RANGE:
LOAD DISTINCT ID Resident INPUT;
JOIN (RANGE)
LOAD Date($(vMin)+Recno()-1) as Date
AutoGenerate $(vMax)-$(vMin)+1;
LEFT JOIN (RANGE)
LOAD * Resident INPUT;
RESULT:
NOCONCATENATE
LOAD ID, Date, Rangesum(if(isNull(Cumulative) and Peek(ID) = ID, Peek(Cumulative), Cumulative)) as Cumulative
Resident RANGE
ORDER BY ID, Date;
DROP TABLE EXT, RANGE, INPUT;