topic If SQL then QVD in QlikView

If SQL then QVD

Wed, 09 May 2012 05:16:14 GMT

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

Wed, 09 May 2012 18:27:10 GMT

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

Thu, 10 May 2012 05:09:55 GMT

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

Thu, 10 May 2012 12:59:47 GMT

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;

If SQL then QVD

Sun, 13 May 2012 23:34:59 GMT

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?

If SQL then QVD

Tue, 15 May 2012 17:35:14 GMT

You could use wildmatch

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

If SQL then QVD

Fri, 18 May 2012 01:29:17 GMT

Thank you so much for your help Marcs

Kind Regards

Aamir