topic Re: Auto Fill next row for same ID in loading script? in QlikView

Auto Fill next row for same ID in loading script?

songzixian2016 — Fri, 05 Oct 2018 19:49:36 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 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

Re: Auto Fill next row for same ID in loading script?

Frank_Hartmann — Fri, 05 Oct 2018 19:57:35 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: Auto Fill next row for same ID in loading script?

vishsaggi — Fri, 05 Oct 2018 20:04:56 GMT

may be try this?

CrxTable:

CrossTable(Phase, EndDate)

LOAD ID,

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;