Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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'))
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
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
Hi, is [N2check equal MP CL] and the other fields having values like 0=false, 1=true?
Check attachment and tell me the differences.
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
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
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
It shoudl work
Silly question but did you have K L and P as dimensions
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