Flag to indicate customer switch from one health plan to another

[cdhdata]

Hi there!

I'm attempting to build a flag in the load script that would show whether a member has the same health plan product in current year as they did in previous year. If they have switched products I want to indicate this with a 'Y.'

See example below:

ID	Year	Product	Switch_Flag
ABC123	2023	Commercial HMO	N
ABC123	2024	Commercial HMO	N
DEF456	2023	Commercial HMO	N
DEF456	2024	Commercial Non-HMO (UBI)	Y
GHI789	2023	Medicare Individual	N
GHI789	2024	Child Health Plus	Y
JKL012	2023	Medicare Individual	N
JKL012	2024	Medicare Individual	N

Any and all help is appreciated! Thanks!

[MatheusC]

Hi, @cdhdata 

Work with the peek function to achieve this result.

https://help.qlik.com/en-US/sense/February2024/Subsystems/Hub/Content/Sense_Hub/Scripting/InterRecor...

Regarts, Matheus

[David_Friend · Accepted solution]

@cdhdata I plugged your question into Chat GPT and it suggested using a combination of preceding loads and resident loads, I won't copy/past the answer here since that would falsely make me look like an expert on this 🙂  But suggest doing it yourself as well may be helpful.

[Vegar]

This will work, but  ensure that the data is loaded in correct order. That is by ID and  YEAR, if the sort order is shuffled then it won't work. You can use ORDER BY ID, YEAR to do this.