topic Nested if/and expression in QlikView

Nested if/and expression

Thu, 20 Nov 2014 13:35:56 GMT

Hi all,

Really hope someone can help me with this one. I've created a formula in Excel which I'm trying to replicate as an expression in Qlik. The formula in Excel reads as:

=IF(AND(K4="False",L4="False",P4="True"),"Decrease CL",(IF(AND(K4="False",L4="True",P4="True"),"Increase CL","OK")))

Which reads and works fantastically. The closest I've been able to come with an expression within Qlik (which still doesn't return the correct results) is:

if([N2check equal MP CL]=False(),if([N2check more than MP CL]=False(),if([Actual Trading > MP Cred Lim]=True(),'Decrease CL',if([N2check equal MP CL]=False(),if([N2check more than MP CL]=True(),if([Actual Trading > MP Cred Lim]=True(),'Increase CL','OK'))))))

(where K = [N2check equal MP CL], L = [N2check more than MP CL] and P = [Actual Trading > MP Cred Lim], just to clarify that I have referenced the right dataset!!)

If anyone can help me code this correctly, it'd be much appreciated!

Kind regards,

Peter Allen

Re: Nested if/and expression

rubenmarin — Thu, 20 Nov 2014 13:43:13 GMT

Hi Peter, try:

If(not [N2check equal MP CL] and not [N2check more than MP CL] and [Actual Trading > MP Cred Lim], 'Decrease CL',

If(not [N2check equal MP CL] and [N2check more than MP CL] and [Actual Trading > MP Cred Lim], 'Increase CL', 'OK'))

Re: Nested if/and expression

oscar_ortiz — Thu, 20 Nov 2014 13:43:43 GMT

Peter,

Give this a try:

If( K4='False' and L4='False' and P4='True', 'Decrease CL',

If( K4='False' and L4='True' and P4='True', 'Increase CL', 'OK' )

)

Good luck

Oscar

Re: Nested if/and expression

Thu, 20 Nov 2014 13:52:14 GMT

Hi Ruben,

Thanks for your quick reply however unfortunately it's still not pulling the correct responses; the return I'm getting for every line of data is "OK" which definitely shouldn't be the case. I have a feeling it could just be the grammar/syntax I'm getting wrong.

Many thanks,

Peter

Re: Re: Nested if/and expression

rubenmarin — Thu, 20 Nov 2014 13:58:16 GMT

Hi, is [N2check equal MP CL] and the other fields having values like 0=false, 1=true?

Check attachment and tell me the differences.

Re: Nested if/and expression

Thu, 20 Nov 2014 13:59:11 GMT

Hi Oscar,

No joy I'm afraid. I replaced the Excel references with the field names and same as with Ruben's suggestion, the only returns I had was "OK", as if that's the over-riding response. I checked my logic again and that definitely shouldn't be the case!

Thanks for the suggestion!

Peter

Re: Nested if/and expression

Thu, 20 Nov 2014 14:04:38 GMT

Hi Ruben,

That's definitely the correct logic table! I may have just realised what might be an issue:

The "fields" [N2check equal MP CL] etc are actually made from other expressions. If I can give you a breakdown:

[N2check equal MP CL] is made from the expression If([N2 Credit Limit] = [MP Credit Limit],True(),False())

[N2check more than MP CL] is made from the expression If([N2 Credit Limit] > [MP Credit Limit],True(),False())

[Actual Trading > MP Cred Lim] is made from the expression If([Actual Trading] > [MP Credit Limit],True(),False())

Could this be where I'm going wrong? I apologise if I've made a stupid mistake, still a Qlik newbie!

Many thanks,

Peter

Re: Nested if/and expression

rubenmarin — Thu, 20 Nov 2014 14:10:29 GMT

Not sure when using other expressions as fields, you can 'expand' those fields in the expression like:

If([N2 Credit Limit]<>[MP Credit Limit] and [N2 Credit Limit] < [MP Credit Limit] and [Actual Trading] > [MP Credit Limit], 'Decrease CL',

If([N2 Credit Limit]<>[MP Credit Limit] and [N2 Credit Limit] > [MP Credit Limit] and [Actual Trading] > [MP Credit Limit], 'Increase CL', 'OK'))

Can you upload a sample? Maybe is an issue with QV having more than one value in one field so it returns null

Re: Nested if/and expression

robert99 — Thu, 20 Nov 2014 14:14:00 GMT

It shoudl work

Silly question but did you have K L and P as dimensions

Re: Nested if/and expression

Thu, 20 Nov 2014 14:18:13 GMT

Hi Ruben,

I'll give it a go with 'expanding' the fields first. Not sure how I'd be able to upload a sample as the data contains sensitive information and I really don't have the time to go through it and create dummy data! Will let you know how I get on but have to move onto a different project for now.

Thanks so much for your advice, I'll certainly be in touch!

Kind regards,

Peter

Re: Nested if/and expression

Thu, 20 Nov 2014 14:20:30 GMT

Hi RJ,

No, K, L and P are Excel references. I included the Excel formula so you can see what the result I'm trying to get in Qlik is. If you check out my responses to Ruben I explain a bit more about how I created each respective field.

Thanks,

Peter

Re: Nested if/and expression

oscar_ortiz — Thu, 20 Nov 2014 14:42:09 GMT

Sorry Peter, had to run to a meeting.

What are the actual values of K, L and P? Are they actual text saying False and True? Or are they logical representations of True and False?

Re: Nested if/and expression

robert99 — Thu, 20 Nov 2014 14:43:06 GMT

I know. But did you have the field equivalents as dimensions

Re: Nested if/and expression

oscar_ortiz — Thu, 20 Nov 2014 14:56:31 GMT

To follow up on previous question. If they are stored in Excel as TRUE and FALSE then you would have to modfiy the script:

If( K4=0 and L4=0 and P4=1, 'Decrease CL',

If( K4=0 and L4=1 and P4=1, 'Increase CL', 'OK' )

)

QlikView will read the TRUE value as 1 and the FALSE value as 0.