topic Join two tables - only no matched rows in Dashboards & Analytics Discussions

Join two tables - only no matched rows

Anonymous — Thu, 24 Aug 2023 11:26:25 GMT

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.

Re: Join two tables - only no matched rows

anurag — Tue, 29 Aug 2023 16:15:07 GMT

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.

Re: Join two tables - only no matched rows

RADSr — Thu, 31 Aug 2023 13:03:31 GMT

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.