Error in Set expression

[nevilledhamsiri]

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

[vishsaggi]

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?

[nevilledhamsiri]

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

[vishsaggi]

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?

[vishsaggi]

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 (ooxml, embedded labels, table is Sheet1);

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;

[nevilledhamsiri]

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;

[nevilledhamsiri]

Appreciate if this is looked in to!

Regds

Neville

[vishsaggi]

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.

[tomhoch1 · Accepted solution]

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)

[arvind1494]

SUM({<REASON={'CUSTOMER REQUEST'},CLASS_CODE={'MC','M4'},PREMIUM ={"=PREMIUM>CR_PREMIUM" }>}250)  

try this one

[Anil_Babu_Samineni]

Are you still looking for help?