Nested if/and expression

[#1]

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

[rubenmarin]

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

[oscar_ortiz]

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

[#4]

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

[rubenmarin]

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

Check attachment and tell me the differences.

[#6]

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

[#7]

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

[rubenmarin]

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

[robert99]

It shoudl work

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

[#10]

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