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

Order and group by in script

i am trying to do order and group by in script like this 

table_1:
Hierarchy (POLICY_NO, OLD_POLICY_NUMBER,UW_YEAR) LOAD 
     POLICY_NO, 
     OLD_POLICY_NUMBER,   
     POLICY_EFFECTIVE_DATE, 
     POLICY_EXPIRY_DATE ,
     Policy_Effective_Year,
     UW_YEAR,
     Month_Difference
FROM
temp_table.qvd 
(qvd)  ;



table2:
load 
     POLICY_NO, 
     OLD_POLICY_NUMBER,    
     POLICY_EFFECTIVE_DATE, 
     POLICY_EXPIRY_DATE ,
     Policy_Effective_Year,
     UW_YEAR,
     Month_Difference,
     1 as flag

Resident table_1 order by POLICY_NO;
drop table;



table3:
load 
     POLICY_NO, 
     OLD_POLICY_NUMBER,    
     POLICY_EFFECTIVE_DATE, 
     POLICY_EXPIRY_DATE ,
     Policy_Effective_Year,
     UW_YEAR,
     Month_Difference,
     MIN(Date(POLICY_EFFECTIVE_DATE)) AS Min_Date
     
Resident table2 group by 
     POLICY_NO, 
     OLD_POLICY_NUMBER,    
     POLICY_EFFECTIVE_DATE, 
     POLICY_EXPIRY_DATE ,
     Policy_Effective_Year,
     UW_YEAR,
     Month_Difference;
     
     
Store table3 into [C:\Users\HP\Desktop\test\table_3.qvd](qvd);
Drop Table table3;

from here i am trying to get Min Date of Policy_Effective_Date against policy no .. but i unable to get it.. 

 

where is the problem 

 

 

Labels (4)
1 Solution

Accepted Solutions
asinha1991
Creator III
Creator III

then do this

 

table3:
load
     POLICY_NO

     MIN(Date(POLICY_EFFECTIVE_DATE)) AS Min_Date
    
Resident table2 group by
     POLICY_NO;

inner join

Load

     OLD_POLICY_NUMBER,   
     POLICY_EFFECTIVE_DATE,
     POLICY_EXPIRY_DATE ,
     Policy_Effective_Year,
     UW_YEAR,
     Month_Difference,

POLICY_NO

Resident table2;

View solution in original post

6 Replies
asinha1991
Creator III
Creator III

are you getting any error?

is POLICY_EFFECTIVE_DATE a date or a text? if text, you need to use date# to convert

 

capriconuser
Creator
Creator
Author

i am not getting any error .. but when  i drag Min Date and Policy No then this shows all dates instead of Min Date.. 

capriconuser
Creator
Creator
Author

this shows me like this 

Min_DateChild Policy
28-10-2013CAR/113480
09-10-2017CAR/113480
01-01-2018CAR/113480
01-04-2018CAR/113480
20-05-2018CAR/113480

 

where as i want to get only min date against policy 

ei.g 

28-10-2013 CAR/113480

asinha1991
Creator III
Creator III

then do this

 

table3:
load
     POLICY_NO

     MIN(Date(POLICY_EFFECTIVE_DATE)) AS Min_Date
    
Resident table2 group by
     POLICY_NO;

inner join

Load

     OLD_POLICY_NUMBER,   
     POLICY_EFFECTIVE_DATE,
     POLICY_EXPIRY_DATE ,
     Policy_Effective_Year,
     UW_YEAR,
     Month_Difference,

POLICY_NO

Resident table2;

capriconuser
Creator
Creator
Author

ok but there is so many synthetic keys.. how i remove that

Brett_Bleess
Former Employee
Former Employee

See the following Design Blog post:

https://community.qlik.com/t5/Qlik-Design-Blog/Synthetic-Keys/ba-p/1472634

Regards,
Brett

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.