order and group by to get min date against policy no

[capriconuser]

This is the script i have 

 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
FROM
temp_table.qvd 
(qvd)  ;

now how i to do order by asc on POLICY_NO and OLD_POLICY_NUMBER and then i  apply group by 

any help please

[capriconuser]

when i comment on load also then how i get field this POLICY_EFFECTIVE_DATE ..because i use this field on another tab

@StarinieriG @Arthur_Fong 

[sunny_talwar]

Can you try this

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

Right Join (Table)
LOAD POLICY_NO,
     Min(POLICY_EFFECTIVE_DATE) as POLICY_EFFECTIVE_DATE
Resident Table
Group By POLICY_NO;

[capriconuser]

@sunny_talwar  where is order by .. i need to order by on POLICY_EFFECTIVE_DATE.. then group by 

[sunny_talwar]

If all you need is a single POLICY_EFFECTIVE_DATE for each POLICY_NO... what is order by needed for? In other words, in your example

Child Policy                Min_Date
CAR/113480                28-10-2013

The order of the above won't change if you do order by Date Ascending or descending if all you expect is a single row per policy. or am I missing a point here?

[capriconuser]

because first i need to order by all policy effective date .. because there is some other things which i have to done .. so first i need to order by dates in table then want to group by  

[sunny_talwar · Accepted solution]

It is a very strange request, but okay

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

FinalTable:
NoConcatenate
LOAD *
Resident Table
Order By ...;

DROP Table Table;

Right Join (FinalTable)
LOAD POLICY_NO,
     Min(POLICY_EFFECTIVE_DATE) as POLICY_EFFECTIVE_DATE
Resident FinalTable
Group By POLICY_NO;

I have left three dots for your Order by field... I don't know what order you want, so feel free to replace ... with the fields you wish to order by 🙂

[capriconuser]

oh yes got it .. thank u .. i am going to try if any probelm i wil let u know @sunny_talwar 

[StarinieriG]

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


Left Join (Table)
LOAD POLICY_NO,
     Min(POLICY_EFFECTIVE_DATE) as MIN_POLICY_EFFECTIVE_DATE
Resident Table
Group By POLICY_NO;
         

so you will not lose other fields

[capriconuser]

is this possible to do this without joins ?

[StarinieriG]

You could use mapping instead of join