topic Re: Order and group by in script in QlikView

Order and group by in script

capriconuser — Sat, 16 Nov 2024 02:16:37 GMT

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

Re: Order and group by in script

asinha1991 — Wed, 09 Oct 2019 09:06:20 GMT

are you getting any error?

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

Re: Order and group by in script

capriconuser — Wed, 09 Oct 2019 09:14:13 GMT

i am not getting any error .. but when i drag Min Date and Policy No then this shows all dates instead of Min Date..

Re: Order and group by in script

capriconuser — Wed, 09 Oct 2019 09:32:14 GMT

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

Re: Order and group by in script

asinha1991 — Wed, 09 Oct 2019 10:10:50 GMT

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;

Re: Order and group by in script

capriconuser — Wed, 09 Oct 2019 10:31:29 GMT

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

Re: Order and group by in script

Brett_Bleess — Mon, 14 Oct 2019 13:46:17 GMT

See the following Design Blog post:

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

Regards,
Brett