Qlik Community

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
QlikWorld 2022, LIVE in Denver CO., May 16-19, 2022. REGISTER NOW TO RECEIVE EARLY BIRD PRICING
cancel
Showing results for 
Search instead for 
Did you mean: 
KevSan
Contributor
Contributor

Left Join

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

1 Reply
Or
Master II
Master II

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;