Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save $300 before February 6: REGISTER NOW!
cancel
Showing results for 
Search instead for 
Did you mean: 
songzixian2016
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
Frank_Hartmann
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
Frank_Hartmann
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; 

vishsaggi
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;