Grouping and wildcard in Editor or expression

[TheresaB_4]

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

[Or]

I believe you're looking for the WildMatch() function.

[Parthiban]

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

[Ravi_Nagmal]

@TheresaB_4 Try this :

If(wildmatch(ProductDescription,'*hydro*'),'Hydro',
if(wildmatch(ProductDescription,'*wind*'),'Wind',
if(wildmatch(ProductDescription,'*zon*'),'Zon',ProductDescription)))

[TheresaB_4]

How would you create something similar like this

[marcus_sommer · Accepted solution]

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

[TheresaB_4]

Awesome it works.
Thanks so much