Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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 !

1 Solution

Accepted Solutions
Not applicable
Author

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;

View solution in original post

6 Replies
Not applicable
Author

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'))

Not applicable
Author

//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

Not applicable
Author

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;

Not applicable
Author

Thank you so much for your help Marcs

I was committing mistake because the names are more longer (e.g NAMECUST='NEWEXRESS(TOWN)') If i do in that way there will loads of names i have to define in if statement.

if (NAMECUST='NEW EXPRESS(town1)','NEWEXPRESS')

if (NAMECUST='NEW EXPRESS(town2)','NEWEXPRESS'))

AS CUSTOMERS;)

Is it possible that I might search by first name only instead of defining all customers in If statement?

Not applicable
Author

You could use wildmatch

so

if(wildmatch(NAMECUST,'NEW EXPRESS*'),'NEW EXPRESS')

Not applicable
Author

Thank you so much for your help Marcs

Kind Regards

Aamir