topic Re: If function in QlikView

If function

Tue, 03 Jul 2012 12:07:01 GMT

So I have many values that I need to assign to a column depending on another column.

if 'Group Description' has 'WR' in it, I need to display 'blank'

if 'Group Description' has 'Support' in it, i need to display 'blank'

etc

So if this one big if statement? And all I wanted to do is search the field group description for specific things, like 'WR' or 'Support' and many others.

Then display something else in my 'Catagory' column.

Can I use wildmatch?

And I would like to do this in the script.

Thanks!

Re: If function

Tue, 03 Jul 2012 13:03:07 GMT

use mapping load

Re: If function

Tue, 03 Jul 2012 13:10:19 GMT

Can you elaborate?

Re: If function

swuehl — Tue, 03 Jul 2012 13:20:23 GMT

A wildmatch should work

if(wildmatch([Group Description],'*WR*','*Support*'), 'blank',[Group Description]) as Category,

You can also look into the QV cookbook on Rob Wunderlich's download page, looking for the mapping table with wildmatch example.

Hope this helps,

Stefan

Re: If function

Tue, 03 Jul 2012 13:44:25 GMT

(wildmatch([GroupDescription],'Non WR*'), 'Improve/Enhance',[GroupDescription]),
if(wildmatch([GroupDescription],'Project' and <> 'Suite 10'), 'Projects',[GroupDescription]

) as Metrics_Catagory

I seem to be doing something wrong here on the last part. I need it to display 'Projects' for everything that has 'Project' in it but doesn't have 'Suite 10' in the name.

Any ideas?

Re: If function

swuehl — Tue, 03 Jul 2012 13:54:20 GMT

It seems that your second if() is never executed, i.e. using two if() statements separated by a comma is not a valid syntax.

Try

(wildmatch([GroupDescription],'Non WR*'), 'Improve/Enhance',
if(wildmatch([GroupDescription],'Project' and <> 'Suite 10'), 'Projects',[GroupDescription]

)) as Metrics_Catagory

Re: If function

Tue, 03 Jul 2012 14:08:01 GMT

It still doesnt seem to work...

(wildmatch([GroupDescription],'Non WR*'), 'Improve/Enhance',
wildmatch([GroupDescription],'Project*' and <> 'Suite 4*'),'Projects',[GroupDescription]

) as Metrics_Catagory

resident TimeLog;

drop table TimeLog;

Re: If function

swuehl — Tue, 03 Jul 2012 14:17:52 GMT

AH SURE your logic in the second if is not correct:

(wildmatch([GroupDescription],'Non WR*'), 'Improve/Enhance',
if(wildmatch([GroupDescription],'Project*') and not wildmatch([GroupDescription],'Suite 4*'),'Projects',[GroupDescription])) as Metrics_Catagory

Please note that your comparison strings only have a wild card at the end, so your GroupDescriptions must start with e.g. Project to match.

For matches with single strings, you could also use like string operator:

([GroupDescription] like 'Non WR*' , 'Improve/Enhance',
if( [GroupDescription] like 'Project*' and not [GroupDescription] like 'Suite 4*','Projects',[GroupDescription])) as Metrics_Catagory

Re: If function

swuehl — Tue, 03 Jul 2012 14:44:36 GMT

Pls check this:

LOAD *,

(wildmatch([GroupDescription],'Non WR*'), 'Improve/Enhance',

if(wildmatch([GroupDescription],'Project*') and not wildmatch([GroupDescription],'Suite 4*'),'Projects',[GroupDescription])) as Metrics_Catagory,

(wildmatch([GroupDescription],'*Non WR*'), 'Improve/Enhance',

if(wildmatch([GroupDescription],'*Project*') and not wildmatch([GroupDescription],'*Suite 4*'),'Projects',[GroupDescription])) as Metrics_Catagory2

INLINE [

GroupDescription

Non WR Test1

Non wr Test2

Non WR sdfdsfsd

Project Suite 4 1

Project Suite 42

Suite 51 Project

Suite 42 Project 2

];

Re: If function

Tue, 03 Jul 2012 14:47:59 GMT

How can I also assign those that say 'Suite 4*' a value for Metrics_Catagory? At the moment, it runs but isnt doing it correctly.

They all start with 'Project' in the GroupDescription, so I want those to show as 'Projects' in Metrics_Catagory. But those that have 'Suite 4' WITHIN the field (it comes after "Project") need to say 'Lean' for Metrics_Catagory.

Right now they all show as 'Projects' in Metrics_Catagory.

Re: If function

swuehl — Tue, 03 Jul 2012 14:54:38 GMT

The wildmatch() functions are just used as conditions in the if() statements. So you essentiall need to think about what your conditions are and what you want THEN to do and what ELSE (in the then / else branches, second and third argument to if() statements).

I am not 100% sure what your requirements are, but you could try something like

LOAD *,

(wildmatch([GroupDescription],'*Non WR*'), 'Improve/Enhance',

if(wildmatch([GroupDescription],'*Project*'),if(wildmatch([GroupDescription],'*Suite 4*'),'Lean','Projects'),[GroupDescription])) as Metrics_Catagory

INLINE [

GroupDescription

Non WR Test1

Non wr Test2

Non WR sdfdsfsd

Project Suite 4 1

Project Suite 42

Suite 51 Project

Suite 42 Project 2

];

Re: If function

Tue, 03 Jul 2012 14:57:52 GMT

That worked perfectly. Thanks for all the help.

I continue the same pattern if I want to format others as well?