topic Multiple If's in QlikView

Multiple If's

agsearle — Thu, 17 Dec 2009 19:03:29 GMT

I need to use a multiple if statement, but I can't seem to get it to work.

I have a column of data that has either a '2', a '6', a '41' or is empty. If i enter the following in a calculated dimension, in a straight table, I get all the 2's and everything else says 'None'.

IF(Lock_Category <> '2', 'None', Lock_Category)

So how can I amend this to say, if the Lock Category is not equal to 2,6 or 41, to show 'None' , but still show the 2's, 6's and 41's.

Thanks.

Multiple If's

bismart — Thu, 17 Dec 2009 19:26:40 GMT

check out the MATCH function.

match( M, 'Jan','Feb','Mar')

returns 2 if M = Feb

returns 0 if M = Apr or jan

Multiple If's

Thu, 17 Dec 2009 19:27:26 GMT

Andrew,

please provide sample data we can play with.

Thank you!

Rainer

Multiple If's

bismart — Thu, 17 Dec 2009 19:31:26 GMT

IF ( Match(Lock_Category ,2,6,41) = 0, 'None',Lock_Category ) as Lock_Category

Multiple If's

agsearle — Thu, 17 Dec 2009 20:03:14 GMT

That didn't work, but this nearly does:

IF(MATCH(Lock_Category,2,6,41)=0, Lock_Category, 'None')

When used as a calculated dimension it places all the 2's and 6's correctly, and empties as 'None', but is also placing a 'None' in the 41's?

Multiple If's

bismart — Thu, 17 Dec 2009 20:27:55 GMT

try

IF(MATCH(Lock_Category,2,6,41, '2', '6', '41')=0, 'None',Lock_Category)

Original suggestion worked for me on test script

Multiple If's

prieper — Thu, 17 Dec 2009 20:28:50 GMT

Might be worth trying to either remove the blank before the closing bracket like ",41)" or to enclose the digits with apostrophs.

Peter

Multiple If's

agsearle — Thu, 17 Dec 2009 21:26:00 GMT

Whichever way I do it, it seems to assume that '41' is 'None', and if I put it in the script, it also makes the 41's 'None', but also leaves all the empty cells empty.

Multiple If's

bismart — Thu, 17 Dec 2009 22:47:51 GMT

Think you must be missing something very basic.... it happens with all of us...

Post some sample data in an app and I'll have a look

sometimes the computer says 'No'

Multiple If's

Miguel_Angel_Baeyens — Thu, 17 Dec 2009 23:43:17 GMT

Hi!

You may be missing comparing Num(Field) with 2,6,41 and all possible values you want to match. You may use as well a mapping load in the load statement. Anyway, bear in mind that QlikView doesn't have "data types". If you want to compare your date field with some dates, you better use date(field) vs. date(value) to ensure matching success...

Multiple If's

prieper — Fri, 18 Dec 2009 17:15:12 GMT

Think that you should recheck your input-data, my guess is that you do not have a "41" there, but " 41" or the like. TRIM-function might be of help then. Function provided by Bismart works. Have added an example to illustrate

HTH
Peter

Multiple If's

Fri, 18 Dec 2009 19:15:56 GMT

you can try this

if (Lock_category <>'2','Non' ,

if (Lock_category <>'6','First' ,

if (Lock_category <>'41','Secon' )))