- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Flatten Data based on ID and Date to show change from and change to
hi all-
I have a table where it contain ID, Date and Email as shown below
load * inline [
ID, Date, EMail
1, 1/1/2023, aa@yahoo.com
1, 1/1/2024, bb@yahoo.com
1,1/1/2025, cc@yah.com
];
I want to present the data to show the change per ID with from and To like below..
ID, Date, From, To
1, 1/1/2023, aa@yahoo.com, null
1, 1/1/2024, aa@yahoo.com, bb@yahoo.com
1,1/1/2025, bb@yahoo.com, cc@yah.com
];
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi,
You can use the 'Previous' inter-record function.
More details here:
One example :
Result table will contain :
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@alec1982 Please refer below script and screenshot.
ABC:
load * inline [
ID, Date, EMail
1, 1/1/2023, aa@yahoo.com
1, 1/1/2024, bb@yahoo.com
1,1/1/2025, cc@yah.com
];
CDE:
Load
ID as ID
, Date as Date
, If(ID=Previous(ID), (EMail), Null()) as Email_To
, if(isnull((Previous(EMail))),EMail,Previous(EMail)) as Email_From
Resident ABC
Order By ID, Date;
Drop Table ABC;