If function

[#1]

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!

[#2]

use mapping load

[#3]

Can you elaborate?

[swuehl]

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

[#5]

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?

[swuehl]

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

[#7]

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;

[swuehl]

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

[swuehl]

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

];

[#10]

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.