JOIN

[botlhokots]

I have the following Qlik script that I am using to try and match data from one table (Parent) to the other (Child). My problem is that I want to include only Order numbers which are currently on the Parent table and not the Child table. I also Want to ensure that I do not have duplicates on the Parent table as well.

Here is the script:

Parent:
LOAD
    OrderNumber                          AS UninvoicedInvNumber,
    RevenueAmount                        AS UninvoicedRevenueValue,
    Invoiced,
    DeliveryDateTimeFrom                          AS UninvoicedLoadDate,
    Date([DeliveryDateTimeFrom], 'MMM-YYYY')           as UninvoicedMonthYear,
    LoadingDateTimeFrom

FROM [Parent.qvd]
(qvd)
WHERE Invoiced = 'No' AND Date(DeliveryDateTimeFrom) >= '2023-07-01' and RevenueAmount > 0;

Child:
LOAD
    OrderNumber            AS UninvoicedInvNumber,
    Country              AS UninvoicedContract,
    Customer            AS UninvoicedCustomerName

FROM [Child.qvd]
(qvd);

// Join the Parent and Child tables based on OrderNumber
LEFT JOIN (Parent)
LOAD
    UninvoicedInvNumber,
    UninvoicedContract
RESIDENT Child;

// Drop the temporary Child table
DROP TABLE Child;

[TauseefKhan]

Hi @botlhokots,

// Load the Parent table with a WHERE NOT EXISTS clause to avoid duplicates
Parent:
LOAD
OrderNumber AS UninvoicedInvNumber,

LoadingDateTimeFrom
FROM [Parent.qvd]
(qvd)
WHERE Invoiced = 'No'  AND Date(DeliveryDateTimeFrom) >= '2023-07-01'
AND RevenueAmount > 0   AND NOT EXISTS(OrderNumber, OrderNumber);

// Load the Child table
Child:
LOAD
OrderNumber AS UninvoicedInvNumber,
Country AS UninvoicedContract,
Customer AS UninvoicedCustomerName
FROM [Child.qvd]
(qvd);

// Create a temporary table to store OrderNumbers from the Child table
TempChild:
LOAD
UninvoicedInvNumber AS TempOrderNumber
RESIDENT Child;

// Join the Parent and Child tables based on OrderNumber
// Exclude OrderNumbers that are in the TempChild table
LEFT JOIN (Parent)
LOAD
UninvoicedInvNumber,
UninvoicedContract
RESIDENT Child
WHERE NOT EXISTS(TempOrderNumber, UninvoicedInvNumber);

// Drop the temporary tables
DROP TABLE Child;
DROP TABLE TempChild;

[botlhokots]

Hi TauseefKhan

This is causing 0 loads on rows.