Creating a new Previous Data Column

eganba07 — Thu, 23 Dec 2021 14:57:54 GMT

Hi! New poster here. I have a dataset that is updated weekly in the database where the new data is appended under the old data with the last column showing the date it was added.

So for instance

ID Date
1 4/1/2021
2 4/1/2021
1 4/7/2021
2 4/7/2021

We need to create a new column for each ID that is equal to the prior week/month value of some of the columns. For example have a column called Category_Cd and we want to track ID's where the Cd has changed from week to week and from month to month. An example of how it would look below.

ID Date Cat_Cd Prev_Cat
1 4/1/2021 A
2 4/1/2021 A
1 4/7/2021 B A
2 4/7/2021 A

I am confused as to how to build this out in the load editor. I have a general idea how to do it using SQL logic but I feel like it is a case where it is very different from what Qlik offers. Unfortunately I cannot give a test set for everyone here as m Qlik access is on the client side and I cannot pull anything off there. Does anyone have any insight?

Re: Creating a new Previous Data Column

Dalton_Ruer — Wed, 21 Apr 2021 14:18:19 GMT

Perfect use case for the Qlik Script function PREVIOUS. Just be sure that you sort the data correctly so it's in the order you wish. https://help.qlik.com/en-US/sense/February2021/Subsystems/Hub/Content/Sense_Hub/Scripting/InterRecordFunctions/Previous.htm

Data:
Load * Inline [
ID, Date , Cat_Cd
1, 4/1/2021 , A
2, 4/1/2021 , A
1, 4/7/2021 , B
2, 4/7/2021 , C
];

Join (Data)
Load
ID,
Date,
Cat_Cd,
If (Previous(ID) = ID, Previous(Cat_Cd), NULL()) as Previous_Cat_Cd
Resident Data
Order by ID, Date;

Yields the following results:

topic Creating a new Previous Data Column in Connectivity & Data Prep

Creating a new Previous Data Column

Re: Creating a new Previous Data Column