Group Statement Error

[nevilledhamsiri]

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

[Taoufiq_Zarra · Accepted solution]

@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

[Arslan_Ahmed · Accepted solution]

//////////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 :

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],

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);

[nevilledhamsiri]

Fine, it works now!

Thanks

[nevilledhamsiri]

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