Exist or join with multiple tables

[nburton78]

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;

[rubenmarin]

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);

[nburton78]

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?

[rubenmarin]

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

[jonnaamb]

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.

[nburton78]

RubenMarin:

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

[nburton78]

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