[Qlik Cloud] How to rewrite SQL CASE WHEN into Qlik syntax?

[Golem]

Since my SQL code contains business logic and private information about sales data I can't share the full code.

Here are some example lines:

CASE
    WHEN PRODUCTNAME  LIKE '%hand used vaccum cleaner%'
     AND PRODUCTGROUP = 'Vacuum'
        THEN 'Cleaning and Household'
    WHEN PRODUCTGROUP IN ('Smartwatch', 'Toshiba', 'Healthcare', 'Toothbrush', 'Bodycare', 'Tanner', 'Hair Drier')
        THEN 'Comfort'
    WHEN PRODUCT_LEVEL_10 IS NOT NULL
        THEN PRODUCT_LEVEL_10
        ELSE 'ERROR'
END
    AS PRODUCTLEVEL_A

The full statement has 20+ WHEN checks in this format.

How can I rewrite that in modern readable, debuggable Qlik syntax?

[Stoyan_Terziev]

There are several ways to do that. I suspect your code is just an example, so here is a solution that should hint you in couple other ways you can solve it too.

Coalese(
Pick(
	Wildmatch(
		[PRODUCTNAME]&'-'&[PRODUCTGROUP], 
		'*hand used vaccum cleaner*-Vacuum', 
		'Smartwatch-*', 
		'Toshiba-*', 
		'Healthcare-*', 
		'Toothbrush-*', 
		'Bodycare-*', 
		'Tanner-*', 
		'Hair Drier-*'
		),
'Cleaning and Household',
'Comfort',
'Comfort',
'Comfort',
'Comfort',
'Comfort',
'Comfort',
'Comfort'), PRODUCT_LEVEL_10, 'ERROR')			AS PRODUCTLEVEL_A

[Golem]

Thanks but this is as far from "readable" as it gets. Not useable.

[LDR]

Hi @Golem 

Have you tried with Ifs?

If(PRODUCTNAME LIKE '*hand used vaccum cleaner*' AND PRODUCTGROUP = 'Vacuum','Cleaning and Household',
If(Match(PRODUCTGROUP,'Smartwatch', 'Toshiba', 'Healthcare', 'Toothbrush', 'Bodycare', 'Tanner', 'Hair Drier') > 0, 'Comfort',
If(IsNull(PRODUCT_LEVEL_10) = 0, PRODUCT_LEVEL_10,'ERROR')
)
)

Instead of LIKE you can use the following functions: Match() or WildMatch().

Regards

[Golem]

Thanks but nested ifs are not readable or maintainable.

I would expect something like pattern matching in this century but yeah, Qlik is from the 90s it seems.

[Stoyan_Terziev]

If you look closely, I used pattern matching. The special character that Qlik uses is asterisk (*).

If you want to create a mapping function and a configurable table with values and their mapping pattern you can do that but the principle remains the same as I showed in the example.

Neither PBI nor Tableau has what you are looking for out of the box.

I'm interested for how you solved the problem in a more Readable and Useable way.

[marcus_sommer]

I suggest you develop a more readable and maintainable solution in SQL as the used case-approach and then come back and we adapt it within Qlik.

[Golem]

The SQL code I posted was not my code but from my predecessor but you are right it's a total mess in terms of readability and maintainability.
Has to be replaced with Qlik syntax anyway due to manager reasons and it's a big chunk of code so I didn't bother with reformatting when it gets replaced anyway.

Reformatted it now.