Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I have a table with format below:
A, B, C columns indicates the [end date] of corresponding phase, for example, the end date of phase A for ID1 is 4/21/2014
ID | A | B | C |
1 | 4/21/2014 | 4/30/2015 | 5/1/2015 |
2 | 1/1/2014 | 3/1/2015 | 4/1/2015 |
I would like to convert table in this format when loading: (for each phase and each ID, End Date stays the same, Start Date default to previous phase End Date (if available)). Noted that the original table I'm working on contains thousands more IDs, when ID changed, the previous row end date is expected to NOT fill in next row's start date. In this case "ID2 PhaseA Start Date" should remain Blank.
ID | Phase | Start Date | End Date |
1 | A | 4/21/2014 | |
1 | B | 4/21/2014 | 4/30/2015 |
1 | C | 4/30/2015 | 5/1/2015 |
2 | A | 1/1/2014 | |
2 | B | 1/1/2014 | 3/1/2015 |
2 | C | 3/1/2015 | 4/1/2015 |
Thanks for helping me.
Sherrie
Source:
Crosstable (Phase, [End Date], 1)
LOAD * INLINE [
ID, A, B, C
1, 4/21/2014, 4/30/2015, 5/1/2015
2, 1/1/2014, 3/1/2015, 4/1/2015
3, 5/1/2014, 7/1/2015, 9/1/2015
];
Load
ID,
Phase,
if(Phase<>'A',previous([End Date]),Null()) as [Start Date],
[End Date]
Resident Source;
DROP Table Source;
Source:
Crosstable (Phase, [End Date], 1)
LOAD * INLINE [
ID, A, B, C
1, 4/21/2014, 4/30/2015, 5/1/2015
2, 1/1/2014, 3/1/2015, 4/1/2015
3, 5/1/2014, 7/1/2015, 9/1/2015
];
Load
ID,
Phase,
if(Phase<>'A',previous([End Date]),Null()) as [Start Date],
[End Date]
Resident Source;
DROP Table Source;
may be try this?
CrxTable:
CrossTable(Phase, EndDate)
LOAD ID,
A,
B,
C
FROM
[https://community.qlik.com/thread/316073]
(html, codepage is 1252, embedded labels, table is @1);
NoConcatenate
Final:
LOAD *,
If(ID = Previous(ID), Peek('EndDate'), '') As StartDate
Resident CrxTable;
Drop Table CrxTable;