topic Re: Script to transform table? in QlikView

Script to transform table?

songzixian2016 — Fri, 05 Oct 2018 03:11:37 GMT

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

Re: Script to transform table?

shwethaa — Fri, 05 Oct 2018 05:10:49 GMT

Try below script-

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

From source;

Re: Script to transform table?

techvarun — Fri, 05 Oct 2018 05:22:28 GMT

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;

Re: Script to transform table?

songzixian2016 — Fri, 05 Oct 2018 19:43:48 GMT

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?

Re: Script to transform table?

Frank_Hartmann — Fri, 05 Oct 2018 19:56:08 GMT

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;

Re: Script to transform table?

shwethaa — Sat, 06 Oct 2018 02:02:33 GMT

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

Re: Script to transform table?

captain89 — Sun, 07 Oct 2018 13:29:57 GMT

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.