Order and group by in script

[capriconuser]

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 

[asinha1991]

are you getting any error?

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

[capriconuser]

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]

this shows me like this 

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

where as i want to get only min date against policy 

ei.g 

28-10-2013 CAR/113480

[asinha1991 · Accepted solution]

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]

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

[Brett_Bleess]

See the following Design Blog post:

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

Regards,
Brett