Create New Filed in Data Load

[Upali_Wijearatne]

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.

[rubenmarin]

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.