Create New Filed in Data Load

Upali_Wijearatne — Thu, 03 Apr 2025 04:15:03 GMT

Hi Friends

I have below dataset in my qlikview file and I want add new field(Flag) in my data load script. In this script I need add flag field as follows .

I same policy no continues for 3 continuous years I want flag to be '3NP' if two years '2NP' else 'NA' and all this particular Flag should be recorded all records. I have shown expected result next to my data set. Kindly help me to write the script please.

Re: Create New Filed in Data Load

rubenmarin — Thu, 03 Apr 2025 09:16:04 GMT

Hi, if the same policy is interrupted for a year it could be more complicated, if not you can count the distintc year for each policy_no and the do a left join

Left Join ([PolicyTable]) LOAD Policy_No, If(tmpCount=1, 'NA', tmpCount &'NP') as Flag ; LOAD Policy_No, Count(Year) as tmpCount Resident [PolicyTable] Group by Policy_No;

If it can be interrupted you will need a sorted load by Policy_No and Year, each row checking if the previuos is the same policy and year-1, if it is, then add one to tmpCount, else it starts again on 1:

tmpCount: LOAD Policy_No, Year If(Peek('Policy_No')=Policy_No and Peek('Year')=Year-1 ,Peek(tmpCount)+1 ,1) as tmpCount Resident [PolicyTable] Order by Policy_No, Year;

And then you'll need to load by year descendat to apply the largest

Left Join ([PolicyTable]) LOAD Policy_No, Year, If(Peek('Policy_No')=Policy_No and Peek('Year')=Year+1 ,Peek('Flag') ,If(tmpCount=1 ,'NA' ,'NP'& tmpCount)) as Flag Resident [PolicyTable] Order by Policy_No, Year desc;

I didn't tested, maybe I missed something on the logic.

topic Create New Filed in Data Load in App Development

Create New Filed in Data Load

Re: Create New Filed in Data Load