topic Re: target_lookup on same field doesn't work in Qlik Replicate

target_lookup on same field doesn't work

Ole_Dufour — Mon, 02 Oct 2023 13:34:58 GMT

I'm trying to set a value in case of an update on the source.

REFERENCE_EXPORT should be set to 'value is null' when REFERENCE_EXPORT = null.

REFERENCE_EXPORT should be set to 'valeur was not null :' concatenated with its current value when REFERENCE_EXPORT is not null.

For some reason in either case the value is set to 'valeur was not null :'. This surprises me because whenever the value of REFERENCE_EXPORT is null it should be set to 'value is null'.

Our target table is 'PERSONNE_PHYSIQUE' which contains a column 'SBL__S_CONTACT__ROW_ID' which is mapped to $ROW_ID of the source table.

What am I overlooking ? I have tried with and without ifnull() in my transformation, same result.

case ifnull(target_lookup('NO_CACHING','BPU','PERSONNE_PHYSIQUE','REFERENCE_EXPORT','SBL__S_CONTACT__ROW_ID=:1',$ROW_ID),'null') when 'null' then 'value is null' else 'valeur was not null :' || ifnull(target_lookup('NO_CACHING','BPU','PERSONNE_PHYSIQUE','REFERENCE_EXPORT','SBL__S_CONTACT__ROW_ID=:1',$ROW_ID),'null') end

case target_lookup('NO_CACHING','BPU','PERSONNE_PHYSIQUE','REFERENCE_EXPORT','SBL__S_CONTACT__ROW_ID=:1',$ROW_ID) when null then 'value is null' else 'valeur was not null :' || target_lookup('NO_CACHING','BPU','PERSONNE_PHYSIQUE','REFERENCE_EXPORT','SBL__S_CONTACT__ROW_ID=:1',$ROW_ID) end

Re: target_lookup on same field doesn't work

jubli24 — Mon, 02 Oct 2023 13:44:44 GMT

I swear half the errors I run into with my excel reports involves source data in the wrong format. I use that text to column feature constantly. Select column, click the text to column button, click next, click next, select the text radio button (or occasionally date), and hit okay.

Re: target_lookup on same field doesn't work

Ole_Dufour — Mon, 02 Oct 2023 13:58:04 GMT

I just found out that it works when we evaluate against an empty string :

case target_lookup('NO_CACHING','BPU','PERSONNE_PHYSIQUE','REFERENCE_EXPORT','SBL__S_CONTACT__ROW_ID=:1',$ROW_ID) when '' then -- we evaluate against an empty string 'value is null' else 'valeur was not null :' || target_lookup('NO_CACHING','BPU','PERSONNE_PHYSIQUE','REFERENCE_EXPORT','SBL__S_CONTACT__ROW_ID=:1',$ROW_ID) end