Set Analysis for IF statement

[#1]

Can you please help me on the following IF statements to convert to SET ANALYSIS in QLIKView

IF (Transaction_Value =100 then 10 * 2

IF (Transaction _Value = 200 then 10 * 3

IF( Transaction status in ( Clearing, Authorised) then OK))).

Thanks

Ben

[#2]

Hi Ben

What's the purpose of this expression? I don't think it's a good candidate for set analysis

Lukasz

[Anonymous]

Agree with Lukasz Mastalerz, there is nothing here for set analysis.  But it can be written at least with a valid QV syntax:

IF (Transaction_Value =100, 200,

IF (Transaction _Value = 200, 300,

IF( match("Transaction status", 'Clearing', 'Authorised'), 'OK')))

Regards,

Michael

[#4]

Hello Friends,

The Earlier was an example.

The Scenario is something as below.

I Want to basically calculate commission - Here commission is an expression.Now based on the Certain dimesions I would be calculating the Commission. So my actual expression looks like this below.

IF(InsuranceGroup LIKE '*MC*',TotalAmount*0.05,

IF(InsuranceGroup LIKE '*VISA*',TotalAmount*0.10,

IF(InsuranceGroup LIKE '*MY*',TotalAmount*0.20,

IF(InsuranceGroup LIKE '*IN*',TotalAmount*10,

IF(InsuranceGroup LIKE '*MPH*',TotalAmount*9,

IF(InsuranceGroup LIKE '*KKSG*',TotalAmount*0.08,0 -

If All these conditions does not satisfy then the commission should be 0.

And it works But I Want to do it in SET Analysis .Because iam getting lot of memory issues.

Please let me know if still not clear.

Thanks in advance.

Ben

[Anonymous]

Try this:

TotalAmount * pick(wildmatch('*MC*','*VISA*','*MY*','*IN*','*MPH*','*KKSG*','*'), 0.05,0.10,0.20,10,9,0.08,0))

or maybe:

sum(TotalAmount * pick(wildmatch('*MC*','*VISA*','*MY*','*IN*','*MPH*','*KKSG*','*'), 0.05,0.10,0.20,10,9,0.08,0)))

I'm not sure about 0, you'll tell me...

[#6]

Can you please provide me the full set analysis expression.

Zero is something where when none of the IF conditions satisfies. The commission has to be 0.

[Anonymous]

You never tried my pick(match()) expressions, did you?  It is as full as it can be... .  I have reason to believe that it works, and the 0s are taken care of.

Please don't reply before you try it.  If it fails - upload a sample application.

[#8]

Hi Micheal,

Its working. Regarding 0 - IF(InsuranceGroup LIKE '*KKSG*',TotalAmount*0.08,0 - So here if it is KKSG then total amount*0.08 else 0.

Iam currently loading the complete set of data to check the performance. i have just started the load to see. earlier i was getting out of memory issue

Thanks

[#9]

Hi Micheal,

Thanks its working. I have one more issue,

sum(TotalAmount * pick(wildmatch('*MC*','*VISA*','*MY*','*IN*','*MPH*','*KKSG*','*'), 0.05,0.10,0.20,10,9,0.08,0)))

In this expression iam trying to replace values with variables something like below.

sum(TotalAmount * pick(wildmatch('*MC*','*VISA*','*MY*','*IN*','*MPH*','*KKSG*','*'), $(vfive),vten,0.20,10,9,0.08,0)))

Iam using the following syntax $(vFive). The expression does not have any errors but it does calculate either.

[#10]

Panicker,

Can't you modify your model to associate the different ratio to the different INSURANCE GROUP values so that you just do in the UI : sum(TotalAmount*ThisNewRatio ) ?

Fabrice