topic JOIN in App Development

JOIN

botlhokots — Mon, 08 Jul 2024 12:38:58 GMT

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;

Re: JOIN

TauseefKhan — Mon, 08 Jul 2024 13:02:24 GMT

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;

Re: JOIN

botlhokots — Sun, 14 Jul 2024 18:28:25 GMT

Hi TauseefKhan

This is causing 0 loads on rows.