topic Re: CASE operator Missig in Replicate May 2023 in Qlik Replicate

CASE operator Missig in Replicate May 2023

philipp_ebner — Mon, 14 Aug 2023 10:03:56 GMT

HI,

where can I find the CASE operation inside Qlik Replicate on premises?

The official help article states that it shoul exist, but in the operators list I cannot find it:

Qlik Replicate (Windows):

by manually typing it into the expression window, it allows me to execute the statement by starting the replication task, but it doesn't seem to execute it correctly...

CASE WHEN source_lookup('NO_CHACHING','dbo','CustomerHeader','AVG_Spent','CustomerID=?', $CustomerID)
< $ActualSpent THEN '1'
ELSE '0' END

can anyone help please?

Re: CASE operator Missig in Replicate May 2023

john_wang — Mon, 14 Aug 2023 10:32:11 GMT

Hello @philipp_ebner ,

Welcome to Qlik Community forum and thanks for reaching out here.

Good insight. Looks to me the keywords CASE WHEN THEN ELSE END do not present in GUI editor in all Replicate versions. if you want to add them to GUI designer (and other SQLite supported operators) please raise a feature request in Qlik Community Suggest Features.

When it comes to the expression, it's weird, please open a support ticket, our support team will trace the behavior further. As a workaround, let's modify it a bit to manage it to work (for example):

CASE WHEN source_lookup('NO_CHACHING','dbo','CustomerHeader','avg_spent','id=?', $id)-$actual_spent<=0 THEN '1' ELSE '0' END

Hope this helps.

Regards,

John.

Re: CASE operator Missig in Replicate May 2023

Heinvandenheuvel — Mon, 14 Aug 2023 13:47:55 GMT

I suspect the CASE are written is fine, but the SOURCE_LOOKUP is not. Try the CASE with a simple test 'case' for example 100 < $actual_spend.

What is your Source Endpoint? The way you indicate the bind variables in the CONDition clause (record selection expression) varies by source database.

Replicate User Guide: "4. Select the source_lookup function and configure it as follows (using the native syntax of the source endpoint): "

For Oracle, instead of that question mark (SQL server), try a colon with a number reflecting the argument to provide: "ID = :1"

Hein.

Re: CASE operator Missig in Replicate May 2023

philipp_ebner — Mon, 14 Aug 2023 13:57:21 GMT

Hi John, thanks for your suggested workaround. This seems to be working fine.

Nevertheless I will open a support Ticket to further investigate my errorenous syntax (SQL Server Endpoint).

I guess this can easily be reproduced. Thank you anyway!

Re: CASE operator Missig in Replicate May 2023

Heinvandenheuvel — Tue, 15 Aug 2023 02:48:51 GMT

The return from source_lookup may be seen as a piece of string, which does not compare well against a number.

By using "$a - $b" a numeric conversion is forced.

If you have the time and interest please test:

CASE WHEN ( 0 + source_lookup('NO_CHACHING','dbo','CustomerHeader','AVG_Spent','CustomerID=?', $CustomerID) ) < $ActualSpent THEN '1' ELSE '0' END

This will force a conversion to numbers instead of text for the compare.

Hein.

Re: CASE operator Missig in Replicate May 2023

john_wang — Tue, 15 Aug 2023 10:02:36 GMT

Hello @Heinvandenheuvel ,

Definitely you are right, I cannot agree you more :). We have to do data type conversion explicitly (as you do), or implicitly (my expression).

Regards,

John.

Re: CASE operator Missig in Replicate May 2023

philipp_ebner — Wed, 16 Aug 2023 11:26:10 GMT

Hello Hein,

thank you for your input. Your solution seems elegant and works brillantly fine! Thank you!