Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear Experts!
My issue is this.
I have linked refunded policies with refunded GL transfer balances. The link was basically with Credit note no. I need following conditions to be met & once it is met to compute Rs 250/= for all such cases.Followings are my conditions,
Class code= MC, M4
Reasons=Customer request
Premium< CR_PREMIUM
then to calculate Rs 250/=
To incorporate above, I have applied following set expression but it doesn't produce expected results. Can my dear friends to look in to this & see where I have gone wrong!
SUM({<REASON={'CUSTOMER REQUEST'},CLASS_CODE={'MC','M4'},PREMIUM ={"=PREMIUM>CR_PREMIUM" }>}250)
Regds
Neville
What is your expected output for this expression? I can see synthetic keys in your data. You can create a Key like
AutoNumber(CREDIT_NOTE_NO & REASON) AS %CreditKey in both the tables.
Then your expression for sum where are you looking to display this expression in a straight table or a text object?
I may present what my requirement is!
CLASS_CODE=MC, M4
REASON=CUSTOMER REQUEST
PREMIUM>CR_PREMIUM
When the above three conditions are satisfied , that record needs to calculate Rs 250 as a penalty.
Please help on this.
Regards
Neville
Correct me here, you mean your value 250 is static and it should be displayed as a separate field for every condition you have mentioned. ? Did I understood you correctly?
Try this?
REFUNDS:
LOAD BRANCH,
CLASS_CODE,
PRODUCT_CODE,
POLICY_NO,
NAME_OF_INSURED,
CREDIT_NOTE_NO,
CREDIT_NOTE_DATE,
PERIOD_FROM,
PERIOD_TO,
PREMIUM,
ADDRES,
REASON,
FINANCIAL_INTEREST,
ME_CODE, ME_NAME
FROM
OUTER JOIN (REFUNDS)
REFUNDS_GL:
LOAD
REASON AS GLReason,
CREDIT_NOTE_NO,
DATE,
CR_PREMIUM
FROM
(ooxml, embedded labels, table is [RF(RA)]);
FINAL:
LOAD *,
IF(PREMIUM > CR_PREMIUM AND MATCH(CLASS_CODE, 'MC', 'M4') AND REASON = 'CUSTOMER REQUEST', 250,0) AS PenaltyFlag
Resident REFUNDS;
DROP TABLE REFUNDS;
Dear Vishwarath,
Please see the P/T where Rs 250/= is not charged for all cases which are subject to intended conditions. Only one case looks being subjected
Please see the error.
Sorry for the troubles caused!
Neville
REFUNDS:
LOAD BRANCH,
CLASS_CODE,
PRODUCT_CODE,
POLICY_NO,
NAME_OF_INSURED,
CREDIT_NOTE_NO,
CREDIT_NOTE_DATE,
PERIOD_FROM,
PERIOD_TO,
PREMIUM,
ADDRES,
REASON,
FINANCIAL_INTEREST,
ME_CODE,
ME_NAME
FROM
[..\Refund_Registers(July).xlsx]
(ooxml, embedded labels, table is Sheet1);
OUTER JOIN (REFUNDS)
LOAD REASON AS GL_REASON,
CREDIT_NOTE_NO,
DATE,
CR_PREMIUM
FROM
[..\46200(All Branches(July).xlsx]
(ooxml, embedded labels, table is [RF(RA)]);
FINAL:
LOAD *,
IF(PREMIUM >CR_PREMIUM AND MATCH(CLASS_CODE, 'MC', 'M4') AND REASON = 'CUSTOMER REQUEST', 250,0) AS PenaltyFlag
Resident REFUNDS;
DROP TABLE REFUNDS;
Appreciate if this is looked in to!
Regds
Neville
What are the expressions you used for this pivot table. Can you add Penaltyflag in your dimensions rather than in expressions.
Below is what i can see.
For penaltyFlag value 250.
Not sure if you are trying to obtain a distinct value of 250 for each row in the table or trying get to a value of 250 for each instance. But here are 2 formulas, one for each. I added aggregation functions to the premium comparison.
count(distinct {<REASON={'CUSTOMER REQUEST'},CLASS_CODE={'MC','M4'},PREMIUM ={"=sum(PREMIUM)>sum(CR_PREMIUM)" }>} 1) * 250
sum({<REASON={'CUSTOMER REQUEST'},CLASS_CODE={'MC','M4'},PREMIUM ={"=sum(PREMIUM)>sum(CR_PREMIUM)" }>} 250)
SUM({<REASON={'CUSTOMER REQUEST'},CLASS_CODE={'MC','M4'},PREMIUM ={"=PREMIUM>CR_PREMIUM" }>}250)
try this one
Are you still looking for help?