CASE operator Missig in Replicate May 2023

[philipp_ebner]

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?

[john_wang · Accepted solution]

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.

[Heinvandenheuvel]

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.

[philipp_ebner]

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!

[Heinvandenheuvel · Accepted solution]

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.

[john_wang]

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.

[philipp_ebner]

Hello Hein,

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