Skip to main content
Announcements
Global Transformation Awards submissions are open! SUBMIT YOUR STORY
cancel
Showing results for 
Search instead for 
Did you mean: 
alec1982
Specialist II

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

Labels (1)
2 Replies
SRA
Partner - Contributor II

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 :

SRA_0-1736677589871.png

 

Bhushan_Mahajan
Contributor II

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

Bhushan_Mahajan_1-1736746428930.png