topic Re: Group Statement Error in QlikView

Group Statement Error

nevilledhamsiri — Wed, 25 Nov 2020 16:16:04 GMT

Dear Friends,

I have the attached set of data from which I need to write a group by statement based on the branch field. But when I do this a syntax error comes. I need a separate table to be created for the new condition applied on Paid amount. Is the error due to manner how the script lines are stated or otherwise. Please see to this.

Thanks

Neville

Re: Group Statement Error

Taoufiq_Zarra — Sun, 28 Jun 2020 15:30:08 GMT

@nevilledhamsiri

you just omitted aggregation: sum, max,...

..... LOAD BRANCH, IF(sum(PAID_AMOUNT)>25000,'Furthe to be checked','within the allowed range')AS CLAIM_CAT Resident CLAIM GROUP BY BRANCH;

attached qvw file

Re: Group Statement Error

Arslan_Ahmed — Sun, 28 Jun 2020 15:35:41 GMT

//////////Your original Code////////////////

CLAIM:
LOAD RNUM, BRANCH, REPORTED_DATE, LOSS_DATE, DELAY, CLASS_CODE, PRD_CODE, POLICY_NO,
NAME_OF_THE_INSURED, CLAIM_NO, RISK, BALANCE_BF, MONTHS_PROVISION, PAID_AMOUNT,
UNDER_OVER_PROVISION, BALANCE_CF, PAID_DATE, CLOSED_DATE, CLOSED_STAUS, ADVANCED_PAYMENT,
REMARKS, NO_OF_FINAL_PAYMENTS_IN, NO_OF_ADVANCED_PAYMENTS, PREV_MON_INT_PROV,
REASON_OUTSTANDING, ME_CODE, CONV_CODE, CAUSE_OF_LOSS, [Paid after Recovery],
[Reported after Recovery], [Paid after Recovery(2)], [Reported after Recovery(2)],
[Reported Year]
FROM [D:\DATA FOR GROUP BY STATEMENT.xlsx] (ooxml, embedded labels, table is SOURCE_DATA);

LOAD
BRANCH,
IF(PAID_AMOUNT>25000,'Furthe to be checked','within the allowed range')AS CLAIM_CAT
Resident CLAIM
GROUP BY BRANCH;

\\\\\\\\\\\\\\\\\\\\/////////////////////\\\\\\\\\\\\\\\\\\\\\\\\\\\///////////////////////////\\\\\\\\\\\\\

Use the if statement while loading data :

IF(PAID_AMOUNT>25000,'Furthe to be checked','within the allowed range')AS CLAIM_CAT

FROM [D:\DATA FOR GROUP BY STATEMENT.xlsx] (ooxml, embedded labels, table is SOURCE_DATA);

Re: Group Statement Error

nevilledhamsiri — Sun, 28 Jun 2020 16:52:02 GMT

Fine, it works now!

Thanks

Re: Group Statement Error

nevilledhamsiri — Sun, 28 Jun 2020 16:58:20 GMT

Thanks Ahamed

In fact I needed to get it through a group by statement. Your solution is something in the first data load with a condition being applied. Any how thanks for the reply!

Neville