Join two tables - only no matched rows

[Anonymous]

Hi Everyone,

I would need help, I have both dataset below:

1. Company item - contains all items assigned to company and branch

2. Stock - contains stock value assigned to company, branch and items (there are only items having stock value > 0)

What I need to get is only SKU which do not have stock in table Company item.

When I use aggregation table I get only items containg in table Stock (having stock value)

Please could somebedy help me which filter I suppose to use?

Many thanks.

[anurag]

Can you share join details between 2 tables?

Also, share screen shot or more details about aggregation table that your creating on top of these 2 tables.

[RADSr]

As Anurag mentioned, it's very helpful to have data and a mockup ( even in Excel ) of what you want in order to answer questions.   

That said, I think you want to model company item as the child and join to to stock on the SKU - then filter on stock.sku IS NULL  which will return all the items from company item which do not have a corresponding SKU in the stock table.   

Alternatively, create a formula column in the company item table something like this

 lookup(qty, stock.sku, companyitem.sku) 

and just don't expose the stock table to the business at all.    Or an MV combining the two tables.