Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us to spark ideas for how to put the latest capabilities into action. Register here!
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 (2)
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;