Left Join

KevSan — Wed, 08 Dec 2021 13:31:47 GMT

Hi everybody,

I'm trying to join multiple tables in order to make links between my tables and I'm facing an issue when "creating" a new field.

Table 1 - Invoice

InvoiceId
InvoiceDate
JnlType
InvoiceNo
InvoiceSite

Table 2 - Order

InvoiceId
DeliveryClientNo

Table 3 - Client

DeliveryClientNo
ClientNo

Table 4 - ClientMovement

ID ( which can be obtained with ->Year(InvoiceDate)&JnlType&InvoiceNo&InvoiceSite&ClientNo)

So, I made this but I'm stuck for 2 reasons.

[Invoice]
LOAD
FactId,
FactDate,
FactJournal,
FactNo,
FactSite,
Year(FactDate) &'|'& FactJournal &'|'& FactNo &'|'& FactSite AS ClientDocNo
FROM [lib://.../invoice.qvd] (qvd);

LEFT JOIN([Invoice])
LOAD
InvoiceId,
TEXT(RTRIM(DeliveryClientNo))
FROM [lib://...Order.qvd] (qvd);

LEFT JOIN[Invoice])
LOAD
TEXT(RTRIM(DeliveryClientNo))
FROM [lib://.../Client.qvd] (qvd);

After that, i do not know how to make my link between my CLIENT and INVOICE table and to create the field I need for having the ID of table 4.

Can someone please help me?

Have a nice day

Re: Left Join

Or — Wed, 08 Dec 2021 14:08:42 GMT

Since you've used LEFT JOIN, there is no longer a Client table - its fields would be contained within the Invoice table. Note that the additional fields you need for your key don't appear to be in the load script, so you'll want to add them.

You can e.g.

Invoice_Final:

Load *, Field1 & Field2 as NewKey

Resident Invoices

Drop Table Invoices;

Or you could:

Left Join(Invoices)

Load SomeUniqueID, Field1 & Field2 as NewKey

Resident Invoices;

topic Re: Left Join in App Development

Left Join

Re: Left Join