Conditions based upon dates

capriconuser — Thu, 24 Oct 2019 11:26:36 GMT

I have this data

PolicyNo From Date To Date STATUS W16266 04-10-2018 28-12-2018 NEW WC65266 08-11-2018 28-12-2018 NEW P/000159 01-10-2018 04-10-2018 NEW WC425 16-10-2018 14-12-2018 NEW P56 11-12-2018 07-09-2019 NEW C399 01-06-2018 01-06-2018 new C399 01-06-2018 30-11-2018 new C399 29-11-2018 30-05-2019 NEW C399 01-12-2018 30-05-2019 NEW C399 31-05-2019 30-11-2019 renew

first 4 records should be lost because todate is less than today date and fromdate is not avaible under last 12 months

p56 is new because fromdate is under 12 months

last policy C399 first record date 01-06-2018 is new because there are multiple records records against this policy so when this policy came first time then new .. and other is renew but when last record of this policy todate if todate is less than today date then should be lost otherwise renew ..

this is the condition what i tried but not works

if( todate< date(today()) and Month_Difference_2 <= 12 ,'NEW', if(date(todate) > date(today()) and Min_Max_Diff >= 12,'renew','lost')) as STATUS

this is the script i have

Table: LOAD POLICY_NO, fromdate, todate , Num(fabs ( ( (year( fromdate) * 12) + month( fromdate) ) - ( ((year(today()) * 12) + month(today())) ) )) as Month_Difference_2 FROM temp_table.qvd (qvd); table3: NoConcatenate LOAD * Resident Table Order By fromdate,todate asc; DROP Table Table; left Join (table3) LOAD POLICY_NO, Min(fromdate) as Min_Date, Max(todate) as Expiry_Max_Date , ((max(todate) - min(fromdate))/30) as Min_Max_Diff Resident table3 Group By POLICY_NO; Store table3 into [C:\Users\HP\Desktop\test\table_3.qvd](qvd); Drop Table table3;

topic Conditions based upon dates in QlikView

Conditions based upon dates