Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
//////////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);
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
//////////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);
Fine, it works now!
Thanks
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