topic Re: If/Where Statement in QlikView

If/Where Statement

Wed, 29 Oct 2014 16:25:26 GMT

Hi,

I have a Table where the base has 21 Names but I want to restrict this down to 6.

I have been trying to do this with group statements and calculated dimensions but I am not sure if I am in the correct area.

I want to use :

Where constraint = A Or B Or C etc show me fail count for each

But I dont want to see the others.

Can someone point me in the right direction with the correct expression layout please?

Thanks

Sindy

Re: If/Where Statement

markodonovan — Wed, 29 Oct 2014 16:28:45 GMT

Hi there,

You could try something like:

constraint:

LOAD * INLINE [

Letter

];

Load * from Source

WHERE Exists([Letter]);

Thanks

Mark

www.techstuffy.com

Re: If/Where Statement

jyothish8807 — Wed, 29 Oct 2014 16:40:22 GMT

Hi SCDBuist,

Try like this:

Load *

from source

where match(constraint,'A','B','C');

Regards

Re: If/Where Statement

its_anandrjs — Wed, 29 Oct 2014 18:00:24 GMT

You can try also with source table only.

SourceTab:

LOAD * Inline [

Name

C ] WHERE Match( Name,'A','B','C' );

Regards

Anand

Re: If/Where Statement

Thu, 30 Oct 2014 09:41:48 GMT

Thanks all. With your suggestions above I ended up with the following statement on my dimension limits within the chart which seems to work although the Match comes out as 0,1,2 etc. which I have renamed to the columns, however am I correct to assume that '0' would be the sum of all the other fields I have not matched?? and is there anyway not to include this so I am only showing results 1 -6?

= If(Match ([ConstraintName],'chi','date','regularexpression','opcs4or3code','icd10or9code','healthboardcode')=1,'chi',

if(Match ([ConstraintName],'chi','date','regularexpression','opcs4or3code','icd10or9code','healthboardcode')=2,'Date',

If (Match ([ConstraintName],'chi','date','regularexpression','opcs4or3code','icd10or9code','healthboardcode')=3,'regularexpression',

If (Match ([ConstraintName],'chi','date','regularexpression','opcs4or3code','icd10or9code','healthboardcode')=4,'opcs4or3code',

If (Match ([ConstraintName],'chi','date','regularexpression','opcs4or3code','icd10or9code','healthboardcode')=5,'icd10or9code',

If (Match ([ConstraintName],'chi','date','regularexpression','opcs4or3code','icd10or9code','healthboardcode')=6, 'healthboardcode',

'OtherConstraints'))))))

Re: If/Where Statement

Thu, 30 Oct 2014 13:04:37 GMT

HI Kc, My issue is not fully resolved but almost!

Re: If/Where Statement

Thu, 30 Oct 2014 13:08:14 GMT

Hi,

if you want first 6 names only then

first 6 load *

from table name;

or else if you want any of the 6 names means

Load *

from Tablename

where match(fieldname,'a','b','c','d','e','f');

Re: If/Where Statement

jonathandienst — Thu, 30 Oct 2014 13:13:46 GMT

Your expression is overly complex, you can do just this which will produce the same result:

=If(Match (ConstraintName,'chi','date','regularexpression','opcs4or3code','icd10or9code','healthboardcode'),

ConstraintName, 'OtherConstraints')

If you want to exclude all the others, use this:

=If(Match (ConstraintName,'chi','date','regularexpression','opcs4or3code','icd10or9code','healthboardcode'),

ConstraintName)

and suppress nulls for the dimension.

HTH

Jonathan

Re: If/Where Statement

jyothish8807 — Thu, 30 Oct 2014 13:23:55 GMT

Hi SCD,

Try this:

if(match(constraint,'A','B','C')>0,constraint,"")

pick(match(constraint,'A','B','C'),'A','B','C')

Regards