Incremental load with multiple delete flags

[lost_rabbit]

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;

[Greg_Taffer]

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.

[lost_rabbit]

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

[vighnesh_gawad]

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.

[lost_rabbit]

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
*/

[vighnesh_gawad]

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;