help needed with QS join

[amirmohamed]

Hi All,

I need help with a thing.

I have 3 tables

Calendar   365 dates

Stock    50 products

Sales  30 products    and 40 dates

Calendar is linked to sales based on sales_date and Sales is linked to Stock based on Product_code, they are linked based on same Alias (field names)

My problem is

I have products that are in Stock table that doesn't show their data as there are only 30 products with data on Sales as there's a link between them.

Calendar:

Load *, date as sales_date

Stock:

Load *, product as product_code

Sales:

Load *, code as product_code,

invoice_date as sales_dat

even though that Stock has more products data but because sales has less products field product_code only shows the products that both of tables has,

But i want to show the 20 more products that does are not in Sales but exist in Stock

Would you please be able to help?

Many thanks,

[YoussefBelloum · Accepted solution]

Hi,

start loading your stock and make a left join on the stock table loading the Sales OR start loading sales table and make right join on the sales table loading the stock

like this:

STOCK:

LOAD

FROM...

LEFT JOIN(STOCK)

SALES:

LOAD

FROM...

but if you don't have stock dates, using time axis, you will be able to show only the products present on sales table

[marcus_sommer]

I assume that your problem is caused from any period-selections because if there are none a table with product as dimension and sum(Sales) & sum(Stock) should display all products. You could get a quite similar view by period-selections if you ignored them within the stock-expression like:

sum({< Month =, Year =, AnyOtherCalendarFields = >} Stock)

- Marcus

[amirmohamed]

Hi Youssef,

Thank you for your reply, but I don't want to create a join, i don't want the stock to Join to the Sales, I just want to use the Alias to create that link between them

[YoussefBelloum]

Joining is creating a Link / Creating a Link is joining

I just specified the join type (explicitely)

[amirmohamed]

Thank you very much Youssef it worked for me