topic Re: [Qlik Cloud] How to rewrite SQL CASE WHEN into Qlik syntax? in App Development

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

Golem — Tue, 30 Jan 2024 09:57:12 GMT

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?

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

Stoyan_Terziev — Fri, 15 Sep 2023 12:02:48 GMT

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

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

Golem — Tue, 17 Oct 2023 10:39:16 GMT

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

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

LDR — Tue, 17 Oct 2023 10:48:48 GMT

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

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

Golem — Tue, 17 Oct 2023 10:51:53 GMT

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.

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

Stoyan_Terziev — Wed, 15 Nov 2023 13:49:23 GMT

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.

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

marcus_sommer — Wed, 15 Nov 2023 15:07:05 GMT

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.

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

Golem — Tue, 30 Jan 2024 09:59:51 GMT

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.