Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello All,
I've a problem when trying to generate the following Result Table from my Source Table
Source:
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 |
Result:
ID | LOC | Entry Date | Exit Date |
A1 | A | 10.05.2020 | 12.05.2020 |
A1 | B | 16.05.2020 | |
A2 | A | 01.05.2020 | |
A3 | D | 03.05.2020 | 19.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
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;
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;
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;