Creating a new Previous Data Column

[eganba07]

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?

[Dalton_Ruer]

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/InterRecor...

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: