Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Read about Qlik’s Response to COVID-19 Read the Letter, Join the Group.
New Contributor III

order and group by to get min date against policy no

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

1 Solution

Accepted Solutions
Highlighted

Re: order and group by to get min date against policy no

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

23 Replies
Highlighted
Partner
Partner

Re: order and group by to get min date against policy no

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;
Highlighted
Partner
Partner

Re: order and group by to get min date against policy no

add

"drop table TEMP;"

after the script to remove synthetic table.

Highlighted
New Contributor III

Re: order and group by to get min date against policy no

i want first order by then group by

Highlighted
Partner
Partner

Re: order and group by to get min date against policy no

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

Highlighted
New Contributor III

Re: order and group by to get min date against policy no

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 

 

 

Highlighted
Partner
Partner

Re: order and group by to get min date against policy no

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

Highlighted
New Contributor III

Re: order and group by to get min date against policy no

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

Highlighted
New Contributor III

Re: order and group by to get min date against policy no

Highlighted
Partner
Partner

Re: order and group by to get min date against policy no

Because you have to comment the field in the load too