topic Error in Set expression in QlikView

Error in Set expression

nevilledhamsiri — Wed, 24 Jan 2018 17:25:25 GMT

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

Re: Error in Set expression

nevilledhamsiri — Wed, 24 Jan 2018 17:28:18 GMT

Here, Premium should be > CR_Premium

Re: Error in Set expression

nevilledhamsiri — Thu, 25 Jan 2018 19:10:40 GMT

Hi Dear please help me on this!

Neville

Re: Error in Set expression

pradosh_thakur — Thu, 25 Jan 2018 19:17:57 GMT

try this may be

Make sure your Fields are spelled correctly

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

Re: Error in Set expression

nevilledhamsiri — Fri, 26 Jan 2018 01:25:26 GMT

Hi Pradosh

That too doesn't help me to get the results. Could you please look in to this again

Regards

Neville

Re: Error in Set expression

vishsaggi — Fri, 26 Jan 2018 01:57:00 GMT

What is 250 in your sum() is that Field name?

May be Try below:

= SUM({< REASON={'CUSTOMER REQUEST'},

CLASS_CODE={'MC','M4'},

PREMIUM = {'>CR_PREMIUM'} >} 250)

Re: Error in Set expression

pradosh_thakur — Fri, 26 Jan 2018 04:24:15 GMT

Hi Neville

as Viswarath rightly asked what is 250 ? are you sure your field value are in upper case? I mean case sensitivity might be giving you wrong results.

regards

Pradosh

Re: Error in Set expression

nevilledhamsiri — Fri, 26 Jan 2018 17:12:10 GMT

Hi Vishwarath, Pradosh,

Even the expression proposed by Vishwarath doesn't work. Rs 250 is not a field, If the said conditions are met Rs 250 for such policies to be charged with. That is my requirement. If you propose some other expression or something to be done in the script to get the out put that will be alright.

Please see to this & help me

Regds

Neville

Re: Error in Set expression

vishsaggi — Fri, 26 Jan 2018 17:20:33 GMT

I am unable to open your qvw file. Can you post your script here so that i can just load the files. Please post the actual expression too rather than 250 in your sum expression.

Re: Error in Set expression

nevilledhamsiri — Fri, 26 Jan 2018 17:27:30 GMT

Hi Vishwarath,

Please refer below

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

REFUNDS_GL:

LOAD REASON, CREDIT_NOTE_NO, DATE, CR_PREMIUM

FROM (ooxml, embedded labels, table is [RF(RA)]);

"expressions"

SUM(PREMIUM)

SUM(CR_PREMIUM)

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

Re: Error in Set expression

vishsaggi — Fri, 26 Jan 2018 17:48:00 GMT

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?

Re: Error in Set expression

nevilledhamsiri — Sat, 27 Jan 2018 16:22:00 GMT

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

Re: Error in Set expression

vishsaggi — Sun, 28 Jan 2018 04:45:33 GMT

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?

Re: Error in Set expression

vishsaggi — Sun, 28 Jan 2018 05:00:55 GMT

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;

Re: Error in Set expression

nevilledhamsiri — Sun, 28 Jan 2018 05:26:43 GMT

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;

Re: Error in Set expression

nevilledhamsiri — Sun, 28 Jan 2018 14:36:31 GMT

Appreciate if this is looked in to!

Regds

Neville

Re: Error in Set expression

vishsaggi — Sun, 28 Jan 2018 17:08:15 GMT

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.

Re: Error in Set expression

tomhoch1 — Sun, 28 Jan 2018 18:06:19 GMT

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)

Re: Error in Set expression

arvind1494 — Mon, 29 Jan 2018 07:05:38 GMT

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

try this one

Re: Error in Set expression

Anil_Babu_Samineni — Mon, 29 Jan 2018 13:48:59 GMT

Are you still looking for help?