Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
are you getting any error?
is POLICY_EFFECTIVE_DATE a date or a text? if text, you need to use date# to convert
i am not getting any error .. but when i drag Min Date and Policy No then this shows all dates instead of Min Date..
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
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;
ok but there is so many synthetic keys.. how i remove that
See the following Design Blog post:
https://community.qlik.com/t5/Qlik-Design-Blog/Synthetic-Keys/ba-p/1472634
Regards,
Brett