Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

Auto Fill next row for same ID in loading script?

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
AB C
14/21/20144/30/20155/1/2015 
21/1/20143/1/20154/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
PhaseStart DateEnd Date
1A 4/21/2014
1B4/21/20144/30/2015
1C4/30/20155/1/2015 
2A 1/1/2014
2B1/1/20143/1/2015
2C3/1/20154/1/2015 

 

Thanks for helping me.

Sherrie

1 Solution

Accepted Solutions
Highlighted
Master II
Master II

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; 

View solution in original post

2 Replies
Highlighted
Master II
Master II

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; 

View solution in original post

Highlighted
Champion III
Champion III

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;