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 is 4/21/2014
ID | A | B | C |
1 | 4/21/2014 | 4/30/2015 | 5/1/2015 |
I would like to convert table in this format when loading: (for each phase, End Date stays the same, Start Date default to previous phase End Date (if available))
ID | Phase | Start Date | End Date |
1 | A | 4/21/2014 | |
2 | B | 4/21/2014 | 4/30/2015 |
3 | C | 4/30/2015 | 5/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
];
Load ID,Phase, previous([End Date]) as [Start Date],[End Date]
Resident Source;
DROP Table Source;
Try below script-
Load ID,Phase, previous([End Date]) as [Start Date],[End Date]
From source;
Source:
Crosstable (Phase, [End Date], 1)
LOAD * INLINE [
ID, A, B, C
1, 4/21/2014, 4/30/2015, 5/1/2015
];
Load ID,Phase, previous([End Date]) as [Start Date],[End Date]
Resident Source;
DROP Table Source;
This works for simple case, however, my actual scenario does not just contain 1 row. So when I convert this table
ID | A | B | C |
1 | 4/21/2014 | 4/30/2015 | 5/1/2015 |
2 | 1/1/2014 | 3/1/2015 | 4/1/2015 |
The result using your script results in "ID2 PhaseA StartDate" filled with "ID1 Phase C End Date" while the expected result is "ID2 PhaseA StartDate" to be 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 |
How to modify the script to re resolve this error?
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;
Replace previous([End Date]) with if(previous(ID)=ID,previous([End Date]))
if you have only 3 fields you can do it easily like this:
newtab:
load
rowno()*ID as ID, Phase, 0 as [Start Date], A as [End Date]
resident yourtable;
concatenate
load
(rowno()+1)*ID as ID, Phase, A as [Start Date], B as [End Date]
resident yourtable;
concatenate
load
(rowno()+2)*ID as ID, Phase, B as [Start Date], C as [End Date]
resident yourtable;
drop table yourtable.