topic Re: Max (Attribute Value) in a CDC Process in Qlik Compose

Max (Attribute Value) in a CDC Process

jkardos — Fri, 14 Mar 2025 20:49:07 GMT

In a CDC load environment, how do you maintain an attribute value that represents the maximum or minimum value over a set of records?

For example, if I have an order with three items, I want to create an attribute called MaxItemRequestDate where this value is the max Request date among the three items.

I have tried using a windowing function on both the entity and in the mapping. But, looking at the task list scripts SQL, it looks as if in the CDC process the function will only be performed over the delta rows in the TSTG "staging" table, and not the entire row set like it would be in a full load.

Can I have the best of both worlds ?

Thanks!

Re: Max (Attribute Value) in a CDC Process

shashi_holla — Wed, 27 Jul 2022 15:21:11 GMT

Hi @jkardos

During CDC the scope is just the current set of changed records and it doesn't extend to all records in the table. What we can do is to create Post Loading ETL and have the necessary query/logic there.

Please try and let me know.

Thank you,

Re: Max (Attribute Value) in a CDC Process

Nanda_Ravindra — Mon, 08 Aug 2022 20:18:11 GMT

Hello @jkardos Were you able to try what was suggested by Shashi? If you need help with this, please open

a support case and we can have a meeting and walk you through the steps.

Thanks,

Nanda

Re: Max (Attribute Value) in a CDC Process

jkardos — Mon, 08 Aug 2022 15:28:19 GMT

Yes, Please find the Stored Procedure below. It's used in Post ETL as suggested and passes in the &&1 run number parameter to make sure the Data Marts update by CDC process. Setting "Do not truncate staging tables" enabled, since using TSTG table to minimize records processed.

CEATE PROC [lz].[usp_FactSalesOrderHeaderIsOrderRejected_PostLoadETL] @runno [INT] AS
BEGIN
UPDATE o
SET o.IsOrderRejected = oi_agg.newval,
o.RUNNO_UPDATE = @runno
FROM DV.FactSalesOrderHeader_HUB o
JOIN
(
SELECT DISTINCT oi.FactSalesOrderHeader,
CASE
WHEN MAX(oi.ReasonForRejection) OVER (PARTITION BY oi.SalesDocument_CNK) = '' THEN
'F'
ELSE
'T'
END AS newval
FROM DV.FactSalesOrderItem_HUB oi
WHERE EXISTS
(
SELECT 1
FROM DV.TSTG_FactSalesOrderItem toi
WHERE toi.SalesDocument_CNK = oi.SalesDocument_CNK
)
) oi_agg
ON o.ID = oi_agg.FactSalesOrderHeader
WHERE ISNULL(o.IsOrderRejected, '') <> ISNULL(oi_agg.newval, '');
END;

Re: Max (Attribute Value) in a CDC Process

jkardos — Mon, 08 Aug 2022 15:36:46 GMT

SP is for an attribute other than the example Request Date described originally, but would work in either case.