Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Qlik Certification Exam Sale - 40% off. Register by June 30, 2020. GET DETAILS
New Contributor III

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 

 

 

1 Solution

Accepted Solutions
Highlighted
Contributor III

Re: Order and group by in script

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
Highlighted
Contributor III

Re: Order and group by in script

are you getting any error?

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

 

Highlighted
New Contributor III

Re: Order and group by in script

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

Highlighted
New Contributor III

Re: Order and group by in script

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

Highlighted
Contributor III

Re: Order and group by in script

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

Highlighted
New Contributor III

Re: Order and group by in script

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

Highlighted
Digital Support
Digital Support

Re: Order and group by in script

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.