Qlik Community

Qlik Compose for Data Warehouses Discussions

Discussion board for collaboration on Qlik Compose for Data Warehouses.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
Highlighted
New Contributor

Mapping filters and CDC

Fairly new to Replicate and Compose but I have a small issue.  This one's quite trivial but I'm wondering how it might impact me further down the line with similar but more complex requirements.

I am using Replicate to copy a currency table in to my warehouse (it's an old table!)

Currency CodeDescriptionISO Code
£sterlingGBP
US$US DollarUSD
YUDYugoslav Dinar 

 

Replicating the table in to the warehouse initially is fine.  In Compose, I have a created a project that only needs currencies that have an ISO code and my model has the ISO code as the business key (so it doesn't like any records that have NULL ISO codes) and so I've created a filter in the ETL Mappings that excludes ISO codes that are NULL so they don't appear in my warehouse.

When I change a description of one of the currencies eg "sterling" to "Pound Sterling" in my source tables, this change does not get reflected in the warehouse.  I've looked at the the change tables created by Replicate and it appears they only record the columns that have changed - and if the ISO code hasn't changed the values will be NULL in the change table.

Change Table

ChangeCurrency CodeDescriptionISO Code
Before£sterling 
After£Pound Sterling 

 

I've looked at the generated ETL for the CDC and it appears that the mapping filter to exclude the NULL ISO currencies is getting applied to the change tables that are created by Replicate in the landing area so you get a SQL statement along the lines of

SELECT *

FROM CURRENCIES__ct

WHERE ISO_Code IS NOT NULL;

 

There are probably a number of options such as deleting the records that have no ISO codes (a bit of a cheat and there may be projects that are still interested in these currencies) but what other options do I have in these situations?