Solved: Re: order and group by to get min date against pol... - Qlik Community

capriconuser · ‎2019-10-11

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

sunny_talwar · ‎2019-10-11

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 🙂

View solution in original post

Arthur_Fong · ‎2019-10-11

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

NOCONCATENATE
TABLE:
 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 TEMP
GROUP BY
     POLICY_NO, 
     OLD_POLICY_NUMBER,   
     POLICY_EFFECTIVE_DATE, 
     POLICY_EXPIRY_DATE ,
     Policy_Effective_Year,
     UW_YEAR,
     Month_Difference
ORDER BY
     POLICY_NO, 
     OLD_POLICY_NUMBER;

Arthur_Fong · ‎2019-10-11

add

"drop table TEMP;"

after the script to remove synthetic table.

capriconuser · ‎2019-10-11

i want first order by then group by

Arthur_Fong · ‎2019-10-11

Is there any difference with your expected result if you do groupby first then order?

capriconuser · ‎2019-10-11

this is what i get..

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

and this is i want

Child Policy Min_Date
CAR/113480 28-10-2013

i want only minimum date against policy and OLD_POLICY_NUMBER

StarinieriG · ‎2019-10-11

Hi,

probably you are using group by with POLICY_EFFECTIVE_DATE.

Use group by only with Policy nr for example, than join this new table to the other

capriconuser · ‎2019-10-11

when i comment POLICY_EFFECTIVE_DATE from group by then i receive an error .. invalid expression

capriconuser · ‎2019-10-11

@StarinieriG @Arthur_Fong

StarinieriG · ‎2019-10-11

Because you have to comment the field in the load too

order and group by to get min date against policy no

group by

order by

QlikView scripting