Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Everyone,
I need some help here. I have read a lot of suggestions and I am not sure what I am doing wrong here.
Background:
I have a product description and I would like to group them in 5 categories. In SQL you can do this with the 'like ' command and update a new column called Group. If it does not match the 4 updates then I will update it as 'Other'
In SQL I have this where clause
Update Group = 'Hydro'
from table ABC
WHERE ProductDescription LIKE '%Hydro%'
Update Group = 'Wind'
from table ABC
OR ProductDescription LIKE '%Wind%'
Update Group = 'Zon'
from table ABC
Where ProductDescription LIKE '%Zon%'
In Qlik Sense:
I have tried to do this using a new column in the Editor, but can not seem to get it working.
count(If(ProductDescription='*hydro*','Hydro')) AS GroupHydro,
count(if(ProductDescription='*wind*','Wind')) AS GroupWind,
count(if(ProductDescription='*zon*','Zon')) AS GroupZon ,
count(if(ProductDescription='*co2*','CO2')) AS GroupCO2,
Any suggestions, maybe we can do this as an Expression rather than using the editor?
I tried this in the Expression but it does not work:
If(ProductDescription='*hydro*','Hydro',
if(ProductDescription='*wind*','Wind',
if(ProductDescription='*zon*','Zon',ProductDescription))) AS group
Kind regards
Theresa
IMO it's not really helpful to place this within multiple where-clauses else just creating a new field with something like:
pick(wildmatch(ProductDescription,'*hydro*', '*wind*', '*zon*', '*co2*', '*'),
'Hydro', 'Wind', 'Zon', 'CO2', 'Others') AS Group
Beside this you could use LIKE in Qlik similar to the way in SQL with something:
Field LIKE '*value*'
I believe you're looking for the WildMatch() function.
Hi,
try this in backend,
Count(if(WildMatch(ProductDescription,'*hydro*'),'Hydro')) AS GroupHydro,
Count(if(WildMatch(ProductDescription,'*wind*'),'Wind')) AS GroupWind,
Count(if(WildMatch(ProductDescription,'*zon*'),'Zon')) AS GroupZon,
Count(if(WildMatch(ProductDescription,'*co2*'),'CO2')) AS GroupCO2
@TheresaB_4 Try this :
If(wildmatch(ProductDescription,'*hydro*'),'Hydro',
if(wildmatch(ProductDescription,'*wind*'),'Wind',
if(wildmatch(ProductDescription,'*zon*'),'Zon',ProductDescription)))
How would you create something similar like this
IMO it's not really helpful to place this within multiple where-clauses else just creating a new field with something like:
pick(wildmatch(ProductDescription,'*hydro*', '*wind*', '*zon*', '*co2*', '*'),
'Hydro', 'Wind', 'Zon', 'CO2', 'Others') AS Group
Beside this you could use LIKE in Qlik similar to the way in SQL with something:
Field LIKE '*value*'
Awesome it works.
Thanks so much