- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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:
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
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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;
Taoufiq ZARRA
"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "
(you can mark up to 3 "solutions") 😉
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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;
Taoufiq ZARRA
"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "
(you can mark up to 3 "solutions") 😉
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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;