6 Replies Latest reply: May 17, 2012 9:29 PM by Aamir Jugnu RSS

    If SQL then QVD

      Dear Qlikers,

       

      Help needed for the following code plz plz plz

       

       

      if "Yellow" in {vOESHDT.NAMECUST} then "Colorful"

       

      else if ("SNOOZE" in {vOESHDT.NAMECUST} or "CAPTAIN" in {vOESHDT.NAMECUST}) then "Snooze"

       

      else {vOESHDT.NAMECUST} Others

       

      Many Thanks

      Aamir !

        • If SQL then QVD

          I'm not sure what you're asking here.  If you want that if statement into SQL, then you should use a case statement.

           

          Case

               When vOESHDT.NAMECUST like '%Yellow%' Then 'Colorful'

               When vOESHDT.NAMECUST like '%SNOOZE%' or vOESHDT.NAMECUST like '%CAPTAIN%' Then 'Snooze'

               Else 'Others'

          End

           

          If you want that in Qlikview then you can use the If() function:

           

          If(NameCust='Yellow','Colorful,If(NameCust='SNOOZE' or NameCust='CAPTAIN','Snooze','Others'))

            • If SQL then QVD

              //I tried this code as i have had no background of qlikview or sql still learning nuh cant get right yet.

               

               

              if (NAMECUST='ALDI','ALDI',

                 

              if (NAMECUST= 'YELLOW' or NAMECUST='PALE YELLOW','YELLOW',

                  

              if (NAMECUST='HOLYCANE'or NAMECUST='HOLY CANE','HOLY CANE',

                 

              if (NAMECUST='BELLS','BELLS',

               

              if (NAMECUST='ASIAN' or NAMECUST='HARRIS',' ASIAN HARRIS',

               

              if (NAMECUST='BENCHMARKS','BENCHMARKS',

               

              if (NAMECUST='FORTY BILLS', 'FORTY BILLS',

                   

              if (NAMECUST='NEW EXPRESS', 'NEW EXPRESS','OTHERS')))))))) AS CUSTOMERS;

               

               

               

              //It returns only "Others" value while i wanted to group all other values

                • If SQL then QVD

                  Are the all the results of your field NAMECUST in capitol letters? Qlikview is case sensitive so that for example

                  Aldi is not equal to ALDI. You can use the function "lower()" to change these to lower characters.

                   

                   

                  if (lower(NAMECUST)='aldi','ALDI',

                   

                    

                   

                  if (lower(NAMECUST)= 'yellow' or lower(NAMECUST)='pale yellow','YELLOW',

                   

                     

                   

                  if (lower(NAMECUST)='holycane'or lower(NAMECUST)='holy cane','HOLY CANE',

                   

                    

                   

                  if (lower(NAMECUST)='bells','BELLS',

                   

                   

                   

                  if (lower(NAMECUST)='asian' or lower(NAMECUST)='harris',' ASIAN HARRIS',

                   

                   

                   

                  if (lower(NAMECUST)='benchmarks','BENCHMARKS',

                   

                   

                   

                  if (lower(NAMECUST)='forty bills', 'FORTY BILLS',

                   

                      

                   

                  if (lower(NAMECUST)='new express', 'NEW EXPRESS','OTHERS')))))))) AS CUSTOMERS;