Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
capriconuser
Creator
Creator

Month Diff between max fromdate and min todate

From below data i want to get month difference between whatever the minimum date is in fromdate and whatever the  last  max date in todate.. how i get that . .

POLIYNO	From Date   To Date	 
				
114371	21-05-2019   30-06-2019	   
114371	03-04-2019   30-06-2019	
114371	08-12-2018   30-06-2019	
114371	01-07-2019   31-12-2019	

 

 

Labels (4)
2 Replies
ankitbisht01
Creator
Creator

How you want the output to look like?, please paste required output in next column.

capriconuser
Creator
Creator
Author

POLICYNO	From Date	To Date	   Month_Difference
						
CAR/114371	08-12-2018	30-06-2019	6
CAR/114371	03-04-2019	30-06-2019	2
CAR/114371	21-05-2019	30-06-2019	1
CAR/114371	01-07-2019	31-12-2019	11

In last record 11 because from min date 8-12-2018 and max date 31-12-2019 total 11 months is the difference .. so i want that difference only in last record of each policy 

 

i tried this 

Table:
LOAD POLICY_NO,        
     fromdate, 
     todate ,
      Num(fabs (
    ( (year( fromdate) * 12) + month( fromdate) ) 
  - ( ((year(todate) * 12) + month(todate)) ) 
)) as Month_Difference,
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   
Resident table3
Group By POLICY_NO;   
Store table3 into [C:\Users\HP\Desktop\test\table_3.qvd](qvd);
Drop Table table3;