Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 !
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;
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'))
//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
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;
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?
You could use wildmatch
so
if(wildmatch(NAMECUST,'NEW EXPRESS*'),'NEW EXPRESS')
Thank you so much for your help Marcs
Kind Regards
Aamir