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;
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.
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
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.
Regards, Vighnesh Gawad Connect with me on LinkedIn | GitHub
