Flatten Data based on ID and Date to show change from and change to

[alec1982]

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

[SRA]

Hi,

You can use the 'Previous' inter-record function.

More details here:

https://help.qlik.com/en-US/cloud-services/Subsystems/Hub/Content/Sense_Hub/Scripting/InterRecordFun...

One example :

DataLoad:

load * inline [

ID, Date,     EMail

1,  1/1/2023, 1aa@yahoo.com

2,  1/1/2023, 2aa@yahoo.com

3,  1/1/2023, 3aa@yahoo.com

1,  1/1/2024, 1bb@yahoo.com

2,  1/1/2024, 2bb@yahoo.com

3,  1/1/2024, 3bb@yahoo.com

1,  1/1/2025, 1cc@yah.com

2,  1/1/2025, 2cc@yah.com

2,  1/2/2025, 2dd@yah.com

];

Result:

Load 

  ID                                            as ID

, Date                                          as Date 

, If(ID=Previous(ID), Previous(EMail), Null())  as Email_From

, EMail                                         as Email_To

Resident DataLoad

Order By ID, Date;

Drop Table DataLoad;

Result table will contain :

[Bhushan_Mahajan]

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