topic Re: Three if match statmenst in a row?? in QlikView

Three if match statmenst in a row??

Fri, 06 Nov 2015 11:58:29 GMT

Hi,

I want to write a if match statement for three different values, the compiler does not giva any error but the table only shows Smoker after its created so the last values work. What am I doing wrong? Any tips?

if(Value=1 and (Match(CalculationIdentifier, 37,38)),'Smoker and Snuff user'),IF(Value=1 and (Match(CalculationIdentifier, 37)), 'Snuff user'),IF(Value=1 and (Match(CalculationIdentifier,38)), 'Smoker') as SmokeAndSnuffStatus,

Re: Three if match statmenst in a row??

swuehl — Fri, 06 Nov 2015 12:48:09 GMT

I think this code is creating three fields:

if(Value=1 and (Match(CalculationIdentifier, 37,38)),'Smoker and Snuff user'), // field named like the expression

IF(Value=1 and (Match(CalculationIdentifier, 37)), 'Snuff user'), // field named like the expression

IF(Value=1 and (Match(CalculationIdentifier,38)), 'Smoker') as SmokeAndSnuffStatus,

edit:

And your first line should evaluate to 'Smoker OR Snuff user' since the match will not test for and condition, but return a value >0 if CalculationIdentifier is 37 OR 38.

Re: Three if match statmenst in a row??

Anonymous — Fri, 06 Nov 2015 12:53:36 GMT

if you are getting Smoker only, its mean you are having CalculationIdentifier with 38 only.

Check the data, do you have 37?

Re: Three if match statmenst in a row??

Fri, 06 Nov 2015 12:53:47 GMT

Ok it weird because I thought when setting 'as' I create a table (files as I called it) how do I create one table with three different values like based on the if statement so SmokeAndSnuffStatus table would contain values ''Smoker and Snuff user' , 'snuff user' and 'smoker'?

Re: Three if match statmenst in a row??

Fri, 06 Nov 2015 12:54:34 GMT

Yes I have 37, and values for that

Re: Three if match statmenst in a row??

Anonymous — Fri, 06 Nov 2015 12:56:02 GMT

try this one:

You have closed brackets after every value like 'Snuff user') which is wrong:

try this:

if((Value=1 and (Match(CalculationIdentifier, 37,38))),'Smoker and Snuff user',

IF((Value=1 and (Match(CalculationIdentifier, 37))), 'Snuff user',

IF((Value=1 and (Match(CalculationIdentifier,38))), 'Smoker')))as SmokeAndSnuffStatus,

Re: Three if match statmenst in a row??

swuehl — Fri, 06 Nov 2015 12:57:24 GMT

This expression is evaluated per input record, how would you determine Smoker and Snuff based on a single CalculationIdentifier record? Maybe it might help if you post some sample data.

This should create a field with Snuff / Smoker values:

IF(Value=1 and (Match(CalculationIdentifier, 37)), 'Snuff user',

IF(Value=1 and (Match(CalculationIdentifier,38)), 'Smoker')) as SmokeAndSnuffStatus,

Note the changed brackets.

Re: Three if match statmenst in a row??

maxgro — Fri, 06 Nov 2015 12:59:52 GMT

this is correct from a syntax point of view but I think you can never get the bold result because of the underlined if condition

load

if(Value=1 and Match(CalculationIdentifier, 37, 38),'Smoker and Snuff user',

IF(Value=1 and Match(CalculationIdentifier, 37), 'Snuff user',

IF(Value=1 and Match(CalculationIdentifier,38), 'Smoker',

'other'

))) as SmokeAndSnuffStatus

inline [

Value, CalculationIdentifier

0,37

0,38

0,10

1,37

1,38

1,10

];

Re: Three if match statmenst in a row??

Fri, 06 Nov 2015 13:00:31 GMT

No the smoke and snuff user shows ant not the snuff users nor smokers. And I have both values in the data

Re: Three if match statmenst in a row??

Fri, 06 Nov 2015 13:14:26 GMT

Did not work sorry I only get the 'Smoker and Snuff user' and other not 'snuff user' or smoker. And I have people with only snuff and smoke user value 37 and 38 seperatly any other ides? thx

Re: Three if match statmenst in a row??

swuehl — Fri, 06 Nov 2015 13:18:23 GMT

Have you read my comment(s) about trying to evaluate an AND status of field values based on a single input record?

Re: Three if match statmenst in a row??

rubenmarin — Fri, 06 Nov 2015 13:27:07 GMT

Theodor, that logic won't work, for each record you have one value for CalculationIdentifier, it can be 37, 38 or any other value. If it is 37 or 38 it will return 'Smoker and Snuff user' and the other conditions won't be evaluated, for any other value it returns Null().

The logic to return Smoker and Snuff User should be to have CalculationIdentifier=37 and CalculationIdentifier=38, wich is not possible in only one record.

Maybe you need a second step to have all CalculationIdentifier's of one user in one record and then you can use the 'and' to check the users with both conditions.

Re: Three if match statmenst in a row??

Fri, 06 Nov 2015 13:29:51 GMT

Hi Ruben I understand what you mean I can only have to IF states with the current values thanks

Re: Three if match statmenst in a row??

Fri, 06 Nov 2015 13:30:44 GMT

Worked with two If statements thx the value I can calculate inside the table