Qlik replicate discarding replication filter

[Ole_Dufour]

In the source table we are updating a value. Replication should only execute if SBL__S_CONTACT__PR_GRP_OU_ID exists in both source and destination.

In order to achieve this we configured the filter as follows:

$AR_H_OPERATION ='UPDATE'
and target_lookup('NO_CACHING','BPUSTAGING','PERSONNE_PHYSIQUE','SBL__S_CONTACT__PR_GRP_OU_ID','SBL__S_CONTACT__PR_GRP_OU_ID=:1',$ROW_ID) !=''

SBL__S_CONTACT__PR_GRP_OU_ID is a non unique column in the destination table. This column is mapped with ROW_ID, a primary key in the source table.

Looking in the attrep_apply_exceptions table we are getting a lot of errors like the one below:

DECLARE v_count VARCHAR2(100);
BEGIN UPDATE "BPUSTAGING"."PERSONNE_PHYSIQUE" SET "SBL__S_CONTACT__PR_GRP_OU_ID"='1-1OA4J-2'
, "TELEPHONE_FIXE_NUMERO"=NULL
, "SBL__S_ORG_GROUP__TYPE_CD"=NULL
, "TELEPHONE_FIXE_BLOC_TEL"=NULL
WHERE "SBL__S_CONTACT__PR_GRP_OU_ID"='1-1OA4J-2'
AND "TELEPHONE_FIXE_NUMERO" is NULL
AND "SBL__S_ORG_GROUP__TYPE_CD" is NULL
AND "TELEPHONE_FIXE_BLOC_TEL" is NULL ;

v_count:= SQL%ROWCOUNT;

if v_count = 0 then INSERT INTO "BPUSTAGING"."PERSONNE_PHYSIQUE" ( "TELEPHONE_FIXE_NUMERO","TELEPHONE_FIXE_BLOC_TEL","SBL__S_CONTACT__PR_GRP_OU_ID","SBL__S_ORG_GROUP__TYPE_CD" )
VALUES ( NULL,NULL,'1-1OA4J-2',NULL);
end if;

END;

ORA-01400: impossible d''ins rer NULL dans ("BPUSTAGING"."PERSONNE_PHYSIQUE"."REFERENCE") ORA-06512: ligne 1 ORA-06512: ligne 1

Question :
Why is Qlik trying to replicate the row given that the filter requires the value to exist in the destination table ?

For the sake of completeness I am including a screenshot of the filter and the transformations :

[Heinvandenheuvel · Accepted solution]

My WAG is that the lookup is returning a NULL and not an empty string on non-existing row.

Instead of returning SBL__S_CONTACT__PR_GRP_OU_ID, and NOT really using that value I would return some piece for arbitrary string like "gevonden" and test for != 'gevonden'

Alternatively, try playing with ifnull or coalescence to get a piece of (empty) string if not found.

Mind you, on the whole, I get the impression you are trying to convert Replicate into something it is not really designed to be. It is NOT a programming / processing language, just a data movement tool with some bells and whistles. A change was made on the source - therefor it belongs on the target is my guiding principle. You may need to just replicate to intermediary tables and then apply additional business logic on target, perhaps with a TRIGGER or with a repeating scheduled job.

The price for a lookup is a round-trip to source or target for each and every row. For targets like Snowflake it is much better to trigger it with a bulk of data and let the target figure it out with all data rigth there and full SQL flexibility vs the contrived and limited lookup syntax.

Free advice! Worth every penny.

Hein.

[Ole_Dufour]

Thank you for your reply. We will try the ifnull verification for the  SBL__S_CONTACT__PR_GRP_OU_ID column. You are right about the costly return trip to verify the existence of its value for each row.

I think it would be more efficient to apply the following filter verifying that the previous value differs from the new value . Those 4 values correspond to the columns we included in our transformation.

ifnull($BI__ROW_ID,'null') != ifnull($ROW_ID,'null') or
ifnull($BI__MAIN_PH_NUM,'null') != ifnull($MAIN_PH_NUM,'null') or
ifnull($BI__TYPE_CD,'null') != ifnull($TYPE_CD,'null') or
ifnull($BI__X_MAIN_PH_NUM_LR,'null') != ifnull($X_MAIN_PH_NUM_LR,'null')

Regards, groeten,

Ole Dufour