topic Re: Exist or join with multiple tables in QlikView

Exist or join with multiple tables

nburton78 — Sat, 16 Nov 2024 03:31:29 GMT

I have 3 tables: Purchasing, Sales and Work Orders

Purchasing is lot number and quantity

Sales is lot number and quantity

Work orders is Parent lot and Component lot and Quantity.

I to show work orders only where the parent lot equals the sales lot number and component lot equals the purchasing lot number.

**Don't know if it's relevant, but I need the Purchasing and Sales lot numbers to align, thus

Not sure if I'm not explaining it correct, but the report has to do with imports and exports, I have a list of all the imports (Purchasing) and then I have a seperate list with the exports (sales). The last piece is to look at workorders . During that process a component lot is changed and becomes a different, parent lot. I have a list of all the workorders along with their component lot numbers and parent lot numbers. I was hoping there was a way to look at a workorder and if the component exists in the purchasing table and the parent in the sales table then include it in the workorder table. Is this possible? I have the first part done the workorder table is whats really messing with me.

Purchasing:

Load

lot_Number,

Purchasing_Quantity

From Purchasing.qvd;

Sales:

Load

lot_Number,

Sales_Quantity

From Sales.QVD;

WorkOrders:

Load

Parentlotnumber,

Componentlotnumber,

WOQuantity

from Workorder.QVD;

Re: Exist or join with multiple tables

rubenmarin — Tue, 21 May 2019 08:39:23 GMT

Hi, one option is concatenate all data in one table:

FactTable:

Load

lot_Number,

lot_Number as Componentlotnumber

Purchasing_Quantity,

'Purchase' as FactType

From Purchasing.qvd;

Concatenate(FactTable)

Load

lot_Number,

lot_Number as Parentlotnumber,

Sales_Quantity,

'Sale' as FactType

From Sales.QVD;

Concatenate(FactTable)

Load

Parentlotnumber,

Componentlotnumber,

WOQuantity,

'WorkOrder' as FactType

from Workorder.QVD

Where Exists(Parentlotnumber) and Exists(Componentlotnumber);

Re: Exist or join with multiple tables

nburton78 — Tue, 21 May 2019 13:38:50 GMT

I'm not sure if this is what i'm looking for. I need all from the purchase table, all from the sales table and the subset of the workorder table that occurs in both sales and purchase. Is that clearer?

Re: Exist or join with multiple tables

rubenmarin — Tue, 21 May 2019 13:43:27 GMT

Well, I haven't tested but that's what it should do: olnly load workorders that already ahs loaded a Parentlotnumber in sales and a Componentlotnumberin purchases

Re: Exist or join with multiple tables

jonnaamb — Tue, 21 May 2019 14:56:57 GMT

Hello Nburton78,

I believe this is what you are looking for:

Quantity_Table:

Load

lot_Number as Componentlotnumber,

Purchasing_Quantity,

'0' as Sales_Quantity

From Purchasing.qvd;

Concatenate(Quantity_Table)

Load

lot_Number as Componentlotnumber,

Sales_Quantity

'0' as Purchase_Quantity

From Sales.QVD;

WorkOrders:

NoConcatenate

Load

Parentlotnumber,

Componentlotnumber,

WOQuantity

from Workorder.QVD;

Essentially, you will be having 2 tables, associated with ComponentLotNumber field. I hope this helps.

Re: Exist or join with multiple tables

nburton78 — Tue, 21 May 2019 17:17:25 GMT

RubenMarin:

when I tried this none of my sales or workorder data shows up.

Re: Exist or join with multiple tables

nburton78 — Tue, 21 May 2019 17:34:32 GMT

JONNAMB: That gets the sales data in, but the work order data still doesn't show up.