Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
amirmohamed
Creator
Creator

help needed with QS join

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,

1 Solution

Accepted Solutions
YoussefBelloum
Champion
Champion

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

View solution in original post

5 Replies
YoussefBelloum
Champion
Champion

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
Creator
Creator
Author

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
Champion
Champion

Joining is creating a Link / Creating a Link is joining

I just specified the join type (explicitely)

amirmohamed
Creator
Creator
Author

Thank you very much Youssef it worked for me