Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

If function

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

etc

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!

1 Solution

Accepted Solutions
MVP
MVP

Re: If function

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 *,

if

(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

];

11 Replies
Not applicable

Re: If function

use mapping load

Not applicable

Re: If function

Can you elaborate?

MVP
MVP

Re: If function

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

Not applicable

Re: If function

 

if

(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?

MVP
MVP

Re: If function

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

if

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

)) as Metrics_Catagory

Not applicable

Re: If function

It still doesnt seem to work...

if

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

) as Metrics_Catagory

 

resident TimeLog;

drop table TimeLog;

MVP
MVP

Re: If function

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

if

(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:

if

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

MVP
MVP

Re: If function

Pls check this:

LOAD *,

if

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

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

if

(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

];

Not applicable

Re: If function

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.

Community Browser