{"id":1410,"date":"2024-05-29T07:02:55","date_gmt":"2024-05-29T07:02:55","guid":{"rendered":"https:\/\/craftcookcode.com\/?p=1410"},"modified":"2024-05-29T22:27:51","modified_gmt":"2024-05-29T22:27:51","slug":"qlik-replicate-fight-of-the-filters-who-will-prevail","status":"publish","type":"post","link":"https:\/\/craftcookcode.com\/?p=1410","title":{"rendered":"Qlik Replicate – Fight of the filters. Who will prevail?"},"content":{"rendered":"\n

Background<\/h2>\n\n\n\n

The business is doing their best to keep me on my toes with Qlik Replicate; finding new was to bend and stretch the system and consequently my sanity.<\/p>\n\n\n\n

The initial request was, \u201cCan we overwrite this field in a Qlik Replicate task with a SOURCE_LOOKUP?\u201d<\/p>\n\n\n\n

OK \u2013 we can do this.  I abhor putting ETL logic in Qlik Replicate tasks and wanting to keep them as simple as possible and allow the power and the flexibility of the downstream systems to manipulate data.<\/p>\n\n\n\n

But project timelines were pressing, and I complied with their request.<\/p>\n\n\n\n

Later, they came back to me and requested a to add a filter to the derived field in question.<\/p>\n\n\n\n

And that led to me and our Tech Business analyst scratching our heads. <\/p>\n\n\n\n

If we apply a filter to our focus field; will it use the raw field that is in the table? Or will is use the new lookup field with the same name to base the filter on?<\/p>\n\n\n\n

Testing the filters – setting up<\/h2>\n\n\n\n

To start with; some simple tables in MS-SQL:<\/p>\n\n\n

\nCREATE TABLE dbo.TEST_LOOKUP\n(\n\tACCOUNT_ID INT PRIMARY KEY,\n\tFRUIT_ID INT,\n\tFRUIT_NAME VARCHAR(100),\n\tSOURCE_NAME VARCHAR(100)\n);\n\nGO\n\nCREATE TABLE dbo.FRUITS\n(\n\tFRUIT_ID INT PRIMARY KEY,\n\tNEW_FRUIT_NAME VARCHAR(100)\n)\n\nGO\n\nINSERT INTO dbo.FRUITS VALUES(1, 'NEW APPLES');\nINSERT INTO dbo.FRUITS VALUES(2, 'NEW ORANGES');\n<\/pre><\/div>\n\n\n
A simple Qlik Replicate task was created to replicate from the table dbo.TEST_LOOKUP.<\/p>\n\n\n\n
All columns were brought across instead of FRUIT_NAME. FRUIT_NAME will be overwritten with the source lookup:<\/p>\n\n\n
\nsource_lookup('NO_CACHING','dbo','FRUITS','NEW_FRUIT_NAME','FRUIT_ID =?',$FRUIT_ID)\n<\/pre><\/div>\n\n\n
\"\"<\/figure>\n\n\n\n
To test; a simple insert was added to ensure that the source lookup is working correctly:<\/p>\n\n\n
\nINSERT INTO dbo.TEST_LOOKUP VALUES(1, 1, 'OLD APPLES', 'Truck');\n<\/pre><\/div>\n\n\n
Result:<\/p>\n\n\n
\n{\n    "magic": "atMSG",\n    "type": "DT",\n    "headers": null,\n    "messageSchemaId": null,\n    "messageSchema": null,\n    "message": {\n        "data": {\n            "ACCOUNT_ID": 1,\n            "FRUIT_ID": 1,\n            "SOURCE_NAME": "Truck",\n            "FRUIT_NAME": "NEW APPLES"\n        },\n        "beforeData": null,\n        "headers": {\n            "operation": "INSERT",\n            "changeSequence": "20240529060703760000000000000000005",\n            "timestamp": "2024-05-29T06:07:03.767",\n            "streamPosition": "0071a49f:000f8e09:001c",\n            "transactionId": "6EDBA1FA0E0000000000000000000000",\n            "changeMask": "0F",\n            "columnMask": "0F",\n            "transactionEventCounter": 1,\n            "transactionLastEvent": true\n        }\n    }\n}\n<\/pre><\/div>\n\n\n
Everything is working correctly; FRUIT_NAMES got overwritten with “NEW APPLES” in the json output.<\/p>\n\n\n\n
Testing the filters – placing bets <\/h2>\n\n\n\n
In the CDC task; a new filter was added:<\/p>\n\n\n
\n$FRUIT_NAME == 'NEW ORANGES'\n<\/pre><\/div>\n\n\n
And the following SQL statement was run on the source system:<\/p>\n\n\n
\nINSERT INTO dbo.TEST_LOOKUP VALUES(2, 2, 'OLD ORANGES', 'Fridge');\n<\/pre><\/div>\n\n\n
So – If Qlik Replicate filters on the base table’s field; the change WILL NOT<\/em> be replicated through.<\/p>\n\n\n\n
Likewise if Qlik Replicate is using the new derived field for filter; the change WILL <\/em>come through.<\/p>\n\n\n\n
And the results are…<\/p>\n\n\n
\n{\n    "magic": "atMSG",\n    "type": "DT",\n    "headers": null,\n    "messageSchemaId": null,\n    "messageSchema": null,\n    "message": {\n        "data": {\n            "ACCOUNT_ID": 2,\n            "FRUIT_ID": 2,\n            "SOURCE_NAME": "Fridge",\n            "FRUIT_NAME": "NEW ORANGES"\n        },\n        "beforeData": null,\n        "headers": {\n            "operation": "INSERT",\n            "changeSequence": "20240529061434050000000000000000065",\n            "timestamp": "2024-05-29T06:14:34.050",\n            "streamPosition": "0071a49f:000f901a:0005",\n            "transactionId": "28DCA1FA0E0000000000000000000000",    \n            "changeMask": "17",\n            "columnMask": "17",\n            "transactionEventCounter": 1,\n            "transactionLastEvent": true\n        }\n    }\n}\n<\/pre><\/div>\n\n\n
Qlik will use the derived source lookup field over the original field in the table.<\/p>\n\n\n\n
Conclusion<\/h2>\n\n\n\n
Once again this highlights the danger of putting ETL code into Qlik Replicate tasks. It obscures business rules and can lead to confusion in operations like the scenario above. <\/p>\n\n\n\n
It is best to use Qlik Replicate to get the data out of the source database as quickly and as pure as possible and then use the power of the downstream systems to manipulate the data.<\/p>\n","protected":false},"excerpt":{"rendered":"
Background The business is doing their best to keep me on my toes with Qlik Replicate; finding new was to bend and stretch the system and...<\/p>\n","protected":false},"author":1,"featured_media":1415,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[16],"tags":[36],"class_list":["post-1410","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-qlik-replicate","tag-qlikreplicate"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/craftcookcode.com\/index.php?rest_route=\/wp\/v2\/posts\/1410","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/craftcookcode.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/craftcookcode.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/craftcookcode.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/craftcookcode.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1410"}],"version-history":[{"count":4,"href":"https:\/\/craftcookcode.com\/index.php?rest_route=\/wp\/v2\/posts\/1410\/revisions"}],"predecessor-version":[{"id":1417,"href":"https:\/\/craftcookcode.com\/index.php?rest_route=\/wp\/v2\/posts\/1410\/revisions\/1417"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/craftcookcode.com\/index.php?rest_route=\/wp\/v2\/media\/1415"}],"wp:attachment":[{"href":"https:\/\/craftcookcode.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1410"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/craftcookcode.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1410"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/craftcookcode.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1410"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}