topic Re: Re: Set Analysis for IF statement in QlikView

Set Analysis for IF statement

Fri, 31 Jan 2014 19:03:34 GMT

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

Re: Set Analysis for IF statement

Fri, 31 Jan 2014 19:07:16 GMT

Hi Ben

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

Lukasz

Re: Set Analysis for IF statement

Anonymous — Fri, 31 Jan 2014 19:33:36 GMT

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

Re: Set Analysis for IF statement

Fri, 31 Jan 2014 21:33:56 GMT

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

Re: Set Analysis for IF statement

Anonymous — Fri, 31 Jan 2014 21:44:17 GMT

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...

Re: Set Analysis for IF statement

Fri, 31 Jan 2014 22:04:53 GMT

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.

Re: Re: Set Analysis for IF statement

Anonymous — Fri, 31 Jan 2014 22:29:32 GMT

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.

Re: Set Analysis for IF statement

Fri, 31 Jan 2014 22:45:49 GMT

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

Re: Re: Set Analysis for IF statement

Sat, 01 Feb 2014 19:41:08 GMT

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.

Re: Set Analysis for IF statement

Sat, 01 Feb 2014 21:49:58 GMT

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

Re: Set Analysis for IF statement

Anonymous — Mon, 03 Feb 2014 13:05:41 GMT

Sorry, cannot guess because don't know what's in your variables. It may be

$(vfive)

vfive

'$(vfive)'

$(=vfive)

See this blog, maybe it helps :http://community.qlik.com/blogs/qlikviewdesignblog/2013/11/04/the-magic-of-variables

And just try a little...