Query based Source Type is not processing CDC records in Qlik Compose

[Aldy]

Hello,

I have a table structure like mentioned below.

In order to load data into this table, I am using Query based Source Type for both Full Load and CDC.

Query used (For both Full Load and CDC)

SELECT svcsegment.SVCCALLID,
MAX(IIF(svcsegment.WARRANTYTYPEID <> 0, 1, 0)) as HASWARRANTY,
MAX(IIF(svcsegment.WARRANTYCLAIMID = 0 AND svcsegment.WARRANTYTYPEID <> 0, 1, 0)) as MISSINGCLAIM
FROM LandingDB.dbo.XAP_SVCSEGMENTTABLE svcsegment
GROUP BY svcsegment.SVCCALLID

During Full load, I don't see any issue whereas during CDC load, attributes HASWARRANTY and MISSINGCLAIM are loading with NULL values and due to this we are observing data inconsistency in DWH / Data Mart.

What could be going wrong, as we are using such patterns widely through out in our project.

[shashi_holla]

Hi @Aldy 

Since you are using the Replicate to populate the Landing zone, it's better to add the logic as Replicate transformation and use Compose just to read the data directly from those columns. 

Also verify during CDC if the Compose is able to read the data from the LandingDB table as expected i.e. just do a select on WARRANTYTYPEID, WARRANTYCLAIMID  and if it return the values then can add the logic and check.

Thank you,

[Nanda_Ravindra]

Hello @Aldy Is this still an issue or was Shashi's recommendation help resolve the issue ?

[Aldy]

Hi @Nanda_Ravindra ,

I am still not clear on the explanation provided. May be if a call is scheduled, I can run through the scenario.

[robertcur]

Hi Aldy,

If I understand your question right, then remember that there is a difference in the source when reading FULL_LOAD and CDC.  FULL_LOAD will read from the LANDING table "XAP_SVCSEGMENTTABLE" where all the data is stored.  CDC will look for a table with the name "XAP_SVCSEGMENTTABLE__ct", which only has the changes in from Replicate.  All your data might not be in there and therefore might be missing data to do these type of queries.

Agree with ShashiV_holla, try to do this logic in replicate (or bring the table in and do the logic afterwards: POST_PROCESSING / DATA MART / LINKED COMPOSE or the like).

Hope this adds value.

[Nanda_Ravindra · Accepted solution]

@Aldy query-based source type is not supported for CDC and this is by design.

Thanks,

Nanda