Community Conundrums are live! Learn more

How to find difference in similar rows?

Level 1
How to find difference in similar rows?
Jump to solution
I have a database with a unique ID identifier for each row/record. Rows are created by dates.

For example, say I have 3 rows for borrower #1 for April 1, May 1, and June 1 and their respective rating.

April 1 = A2

May 1 = A1

June 1 = A1



How would I be able to create a column that would identify if the rating has changed from the previous month? So April 1 = no, May 1 = Yes, June 1 = No.



Any help would be appreciated. Thanks.
0 Kudos
2 Replies
Dataiker
Dataiker

If you don't want to code, you have these two recipes that can help you:




  • distinct : to make sure that you have a unique row per month per ID

  • windows : can help with many things, in particular can help you to retrieve the previous value (LAG) in a partition (so you should partition by ID and sort by lonth). To better understand this recipe, have a look at windows functions from the SQL databases.



I hope that will help.

Jeremy, Product Manager at Dataiku
Dataiker
Dataiker
Hello, This is exactly the purpose of the visual window recipe. Here is an explanation of how to use it : https://youtu.be/3VZIwxZl3vg Cheers, Alexandre