11 Replies Latest reply: Jul 3, 2012 10:57 AM by Ethan Beaulieu RSS

    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!

        • Re: If function

          use mapping load

            • Re: If function

              Can you elaborate?

                • Re: If function
                  Stefan Wühl

                  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

                       

                      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?

                        • Re: If function
                          Stefan Wühl

                          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

                            • 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;

                                • Re: If function
                                  Stefan Wühl

                                  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

                                    • Re: If function
                                      Stefan Wühl

                                      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

                                      ];

                                      • 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.

                                          • Re: If function
                                            Stefan Wühl

                                            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

                                            ];