Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

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

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;

6 Replies
Not applicable

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

Not applicable

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

Not applicable

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;

Not applicable

If SQL then QVD

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

If SQL then QVD

You could use wildmatch

so

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

Not applicable

If SQL then QVD

Thank you so much for your help Marcs

Kind Regards

Aamir