Qlik Community

Qlik Compose for Data Warehouses

Discussion board for collaboration on Qlik Compose for Data Warehouses.

Announcements
Now Live: Qlik Sense SaaS Simplified Authoring – Analytics Creation for Everyone: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
KeithEdin
Contributor
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?

1 Solution

Accepted Solutions
Madhavi_Konda
Support
Support

Hi Keith,

What is the source of your replicate task? Is it Oracle?
If you are enabling store changes on a replicate task, the supplemental logging should be on ALL columns logging.
If you enable ALL columns logging, your changes tables will have all columns logged. In your case, ISO_CODE will get the actual values from source.
Please test and let us know about your results.

Thanks,
Madhavi

View solution in original post

3 Replies
Madhavi_Konda
Support
Support

Hi Keith,

What is the source of your replicate task? Is it Oracle?
If you are enabling store changes on a replicate task, the supplemental logging should be on ALL columns logging.
If you enable ALL columns logging, your changes tables will have all columns logged. In your case, ISO_CODE will get the actual values from source.
Please test and let us know about your results.

Thanks,
Madhavi

KeithEdin
Contributor
Contributor
Author

Many thanks.  Yes, this has indeed resolved the issue.

jaswantbro19
Contributor
Contributor

Thank you so much for the reply. Even I had this similar issue and it's resolved now.

Thanks for the reply.

Regards get-mxplayer.in