topic Re: Incremental load with multiple delete flags in App Development

Incremental load with multiple delete flags

lost_rabbit — Wed, 29 Apr 2026 20:54:44 GMT

I have a data load requirement involving two tables:

Customers (Primary Key - Id)
Orders (Primary Key - Id)

My goal is to load all active customers from Customers and their matching active records from Orders. If there are no orders, then those fields should be null in data set (Simple left join condition)

Challenge:

The difficulty I’m facing is correctly handling two deletion flags while still maintaining an incremental load approach.

Kindly help with below script

IF Len('$(QVD_PATH)') = 0 THEN Final: SQL SELECT A.ID, A.NAME, B.RATE, A.IS_CUSTOMER_DELETED, B.IS_ORDER_DELETED, GREATEST(A.LAST_UPDATE_DATE, B.LAST_UPDATE_DATE) AS LAST_UPDATE_DATE FROM Customers A LEFT JOIN Orders B ON A.ID = B.ID AND B.IS_ORDER_DELETED = 0 WHERE A.IS_CUSTOMER_DELETED = 0; Call StoreAndDrop('Final'); ELSE Last_Update_Date: LOAD Timestamp(Max(LAST_UPDATE_DATE)) as Max_LAST_UPDATE_DATE FROM [$(QVD_PATH)](qvd); LET vMaxDate = Peek('Max_LAST_UPDATE_DATE', 0, 'Last_Update_Date'); Temp: SQL SELECT A.ID, A.NAME, B.RATE, A.IS_CUSTOMER_DELETED, B.IS_ORDER_DELETED, GREATEST(A.LAST_UPDATE_DATE, B.LAST_UPDATE_DATE) AS LAST_UPDATE_DATE FROM Customers A LEFT JOIN Orders B ON A.ID = B.ID WHERE GREATEST(A.LAST_UPDATE_DATE, B.LAST_UPDATE_DATE)> '$(vMaxDate)' Concatenate(Temp) LOAD * FROM [$(QVD_PATH)](qvd) where NOT Exists(ID); Final: NoConcatenate LOAD * RESIDENT Temp WHERE IS_CUSTOMER_DELETED = 0 AND IS_ORDER_DELETED = 0; Call StoreAndDrop('Final'); END IF;

Re: Incremental load with multiple delete flags

Greg_Taffer — Wed, 29 Apr 2026 20:21:47 GMT

This Qlik script implements a soft-delete mechanism for incremental QVD loading, filtering out records marked deleted in source SQL tables. It handles full loads when QVD doesn't exist and incremental updates by checking IS_CUSTOMER_DELETED and IS_ORDER_DELETED flags, ultimately storing clean records into a QVD.

Code Breakdown:

Initialization: The IF Len('$(QVD_PATH)') = 0 clause detects if this is the first load (no existing QVD), executing a full initial load.
Incremental Logic (ELSE): Finds the latest LAST_UPDATE_DATE from the existing QVD, then queries the SQL database for records modified after that date.
Deletion Handling: In both scenarios, the script filters records (WHERE IS_CUSTOMER_DELETED = 0 AND IS_ORDER_DELETED = 0) to ensure soft-deleted records in source tables are excluded from the final QVD.
Update Mechanism: Call StoreAndDrop('Final') assumes a routine that stores the Final table and drops it to free memory.

This approach is efficient for large datasets because it only processes new or updated records rather than reloading the entire dataset every time.

This should achieve your goal.

Re: Incremental load with multiple delete flags

lost_rabbit — Wed, 29 Apr 2026 21:16:06 GMT

Incorrect response. In the incremental load logic, I want customers that are active and orders that are active. So if IS_ORDER_DELETED changes from 0 to 1, then i still need all the customer data to display and null for all order related fields which i am not able to achieve with this script

Re: Incremental load with multiple delete flags

vighnesh_gawad — Thu, 30 Apr 2026 04:23:05 GMT

Hi @lost_rabbit ,

Could you please share some sample (dummy) data with these columns along with the expected output in Excel format? That will help clarify the requirement and build logic.

Re: Incremental load with multiple delete flags

lost_rabbit — Thu, 30 Apr 2026 09:13:16 GMT

Let me know if this helps

Customers: LOAD * INLINE [ ID, NAME, IS_CUSTOMER_DELETED, LAST_UPDATE_DATE 1, Alice, 0, 2026-01-01 2, Bob, 0, 2026-01-01 3, John, 0, 2026-01-01 4, Ron, 1, 2026-01-01 ]; Orders: LOAD * INLINE [ ID, RATE, IS_ORDER_DELETED, LAST_UPDATE_DATE 1, 250, 0, 2026-01-06 2, 300, 0, 2026-01-07 3, 150, 1, 2026-01-08 ]; /* Full Load - Expected Result ID | NAME | RATE | LAST_UPDATE_DATE 1 | Alice | 250 | 2026-01-06 2 | Bob | 300 | 2026-01-07 3 | John | NULL | 2026-01-01 */ //Customers (3 & 4) updated Customers: LOAD * INLINE [ ID, NAME, IS_CUSTOMER_DELETED, LAST_UPDATE_DATE 1, Alice, 0, 2026-01-01 2, Bob, 0, 2026-01-01 3, John, 1, 2026-05-05 4, Ron, 0, 2026-05-05 ] ; // Orders (2,3,4) updated Orders: LOAD * INLINE [ ID, RATE, IS_ORDER_DELETED, LAST_UPDATE_DATE 1, 250, 0, 2026-01-06 2, 300, 1, 2026-03-03 3, 150, 0, 2026-03-03 4, 500, 0, 2026-03-03 ]; /* Incremental Load - Expected Result ID | NAME | RATE | LAST_UPDATE_DATE 1 | Alice | 250 | 2026-01-06 2 | Bob | NULL | 2026-03-03 4 | Ron | 500 | 2026-05-05 */

Re: Incremental load with multiple delete flags

vighnesh_gawad — Fri, 01 May 2026 15:41:29 GMT

Hi @lost_rabbit ,

Here you go, you can apply this logic. you will get correct result. You will have to do some changes in script when you are using actual SQL tables.

FullLoad: // Customers Load ID, NAME, IS_CUSTOMER_DELETED, CUST_LAST_UPDATE_DATE Where IS_CUSTOMER_DELETED = 0; LOAD * INLINE [ ID, NAME, IS_CUSTOMER_DELETED, CUST_LAST_UPDATE_DATE 1, Alice, 0, 2026-01-01 2, Bob, 0, 2026-01-01 3, John, 0, 2026-01-01 4, Ron, 1, 2026-01-01 ]; // Orders Left Join (FullLoad) Load * Where IS_ORDER_DELETED = 0; LOAD * INLINE [ ID, RATE, IS_ORDER_DELETED, LAST_UPDATE_DATE 1, 250, 0, 2026-01-06 2, 300, 0, 2026-01-07 3, 150, 1, 2026-01-08 ]; MaxCustDateTable: LOAD Max(CUST_LAST_UPDATE_DATE) as Max_CUST_LAST_UPDATE_DATE Resident FullLoad; LET vCustMaxDate = Peek('Max_CUST_LAST_UPDATE_DATE', 0, 'MaxCustDateTable'); DROP Table MaxCustDateTable; MaxDateTable: LOAD Max(LAST_UPDATE_DATE) as Max_LAST_UPDATE_DATE Resident FullLoad; LET vMaxDate = Peek('Max_LAST_UPDATE_DATE', 0, 'MaxDateTable'); Drop Table MaxDateTable; Customers: LOAD * INLINE [ ID, NAME, IS_CUSTOMER_DELETED, LAST_UPDATE_DATE 1, Alice, 0, 2026-01-01 2, Bob, 0, 2026-01-01 3, John, 1, 2026-05-05 4, Ron, 0, 2026-05-05 ]; // Active (Insert/Update) NewOrUpdatedCustomers: NoConcatenate LOAD * Resident Customers Where LAST_UPDATE_DATE > '$(vCustMaxDate)' AND IS_CUSTOMER_DELETED = 0; // Deleted DeletedCustomers: NoConcatenate LOAD * Resident Customers Where LAST_UPDATE_DATE > '$(vCustMaxDate)' AND IS_CUSTOMER_DELETED = 1; drop table Customers; // Collect IDs to replace/remove UpdatedIDs: LOAD ID as NewIDs Resident NewOrUpdatedCustomers; Concatenate (UpdatedIDs) LOAD ID as NewIDs Resident DeletedCustomers; // Keep only unaffected old records FilteredFullLoad: NoConcatenate LOAD * Resident FullLoad Where NOT Exists(NewIDs, ID); drop table FullLoad; Concatenate (FilteredFullLoad) LOAD ID, NAME, 0 as IS_CUSTOMER_DELETED, LAST_UPDATE_DATE as CUST_LAST_UPDATE_DATE Resident NewOrUpdatedCustomers; DROP Tables UpdatedIDs, NewOrUpdatedCustomers, DeletedCustomers; // Orders (2,3,4) updated Orders: Mapping Load ID, if(IS_ORDER_DELETED = 0, RATE) as NewRate Where LAST_UPDATE_DATE > '$(vMaxDate)'; LOAD * INLINE [ ID, RATE, IS_ORDER_DELETED, LAST_UPDATE_DATE 1, 250, 0, 2026-01-06 2, 300, 1, 2026-03-03 3, 150, 0, 2026-03-03 4, 500, 0, 2026-03-03 ]; FilteredFullLoadFinal: NoConcatenate LOAD ID, NAME, IS_CUSTOMER_DELETED, CUST_LAST_UPDATE_DATE, ApplyMap('Orders', ID, RATE) as RATE, IS_ORDER_DELETED, LAST_UPDATE_DATE Resident FilteredFullLoad; Drop table FilteredFullLoad;