Script to transform table?

[songzixian2016]

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

[shwethaa]

Try below script-

Load ID,Phase, previous([End Date]) as [Start Date],[End Date]

From source;

[techvarun · Accepted solution]

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;

[songzixian2016]

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?

[Frank_Hartmann]

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;  

[shwethaa]

Replace previous([End Date]) with if(previous(ID)=ID,previous([End Date]))

[captain89]

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.