Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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
];
use mapping load
Can you elaborate?
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
if
(wildmatch([GroupDescription],'Non WR*'), 'Improve/Enhance',[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?
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
It still doesnt seem to work...
if
(wildmatch([GroupDescription],'Non WR*'), 'Improve/Enhance',) as Metrics_Catagory
resident TimeLog;
drop table TimeLog;
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
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
];
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.