Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Sai33
Partner - Creator
Partner - Creator

Generate New Columns from Existing Columns

Hello All,

I've a problem when trying to generate the following Result Table from my Source Table

Source:

IDDateLOCType
A110.05.2020AEntry
A112.05.2020AExit
A116.05.2020BEntry
A201.05.2020AEntry
A303.05.2020DEntry
A319.05.2020DExit

 

Result:

IDLOCEntry DateExit Date
A1A10.05.202012.05.2020
A1B16.05.2020 
A2A01.05.2020 
A3D03.05.202019.05.2020

 

Basically, i'm looking to split the field Date from my Source Table into two Fields 'Entry Date' and 'Exit Date' based on another Field Type. 

I've tried using the Previous and also Cross Table method. But, nothing helped me completely.

Any help would be much appreciated.

 

Thanks

Sai

Labels (1)
1 Solution

Accepted Solutions
Taoufiq_Zarra

Maye  be :

Data:

LOAD * INLINE [
    ID, Date, LOC, Type
    A1, 10.05.2020, A, Entry
    A1, 12.05.2020, A, Exit
    A1, 16.05.2020, B, Entry
    A2, 01.05.2020, A, Entry
    A3, 03.05.2020, D, Entry
    A3, 19.05.2020, D, Exit
];

output:
noconcatenate

load ID,LOC,subfield(Splittmp,'|',1) as [Entry Date],subfield(Splittmp,'|',2) as [Exit Date] ;
load ID,LOC, concat(Date,'|') as Splittmp  resident Data group by ID,LOC order by Date;

drop table Data;

 

 

Capture.PNG

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉

View solution in original post

2 Replies
Taoufiq_Zarra

Maye  be :

Data:

LOAD * INLINE [
    ID, Date, LOC, Type
    A1, 10.05.2020, A, Entry
    A1, 12.05.2020, A, Exit
    A1, 16.05.2020, B, Entry
    A2, 01.05.2020, A, Entry
    A3, 03.05.2020, D, Entry
    A3, 19.05.2020, D, Exit
];

output:
noconcatenate

load ID,LOC,subfield(Splittmp,'|',1) as [Entry Date],subfield(Splittmp,'|',2) as [Exit Date] ;
load ID,LOC, concat(Date,'|') as Splittmp  resident Data group by ID,LOC order by Date;

drop table Data;

 

 

Capture.PNG

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
whiteymcaces
Partner - Creator
Partner - Creator

This solution is based on the following assumptions:

1. ID does not contain distinct values;

2. LOC does not contain distinct values;

3. ID and LOC cannot be used as a distinct concatenated field; and

4. ID cannot be in 2 separate LOC at the same time, i.e. it cannot Enter a different LOC until it has Exited the previous LOC.

Script.

Step_One:
Load
ID as ID_Temp,
Date as [Entry Date],
If(Type = 'Entry' And Previous(Type) = 'Exit', Previous(Date)) as [Exit Date],
LOC as LOC_Temp,
Type
Resident SourceTable
Order By ID Desc, Date Desc
;

Drop Table SourceTable;

Result:
Load
ID_Temp as ID,
[Entry Date],
[Exit Date],
LOC_Temp as LOC
Resident Step_One
Where Type = 'Entry'
;

Drop Table Step_One;