Difficult with JOIN!

[#1]

Hi everyone!

I have a problem and I will try to show.

I have one table with sales like that:

Product	Color	Outlet	Quantity	Value	Cost	...
xxxx	yyyy	Example	1	10,00	5,00

And I need to put, in that table a column with the stock. The table with stock is something like that:

Product	Color	Outlet	Stock(quantity)
xxxx	yyyy	Example	10

To solve this problem I thought in:

1) I need a Key first, so I joined three columns  (Product + Color + Outlet) and I named "KEY".

2) I need to JOIN first table with the second table with OUTER JOIN. Because the results of the Join that I need is something like:

Product	Color	Outlet	Quantity	Value	Cost	Stock
xxxx	yyyy	Example	1	10,00	5,00	10
zzzz	hhhh	Example2				5
xxxx	dddd	Example				20

3) I need to show that stock in a Pivot Table in qlikview. But, I don't know how because, when I join the tables, some fields will be without value, so the problem is the filter.

Sorry because my english is poor. If someone have a Idea, I'm all ears and thank you for your attention!

[sunny_talwar]

Where did 2nd and 3rd row come from in your required output? Your data doesn't seem to show that information?

[Anonymous]

use the left keep may be get the output

[Kushal_Chawda]

if you have Product + Color + Outlet as key then Just link the both tables on key , Qlikview will automatically do full outer join

[#5]

Hi Sunny, thanks for your attention, Again! So, I don't know exactly if I understood your question. The 2nd and 3rd row doesn't have information because(I guess that will be the result), the table with stock doesn't have information, so when I join, problably the result will be withou that information. I don't know if I answered the question.

[#6]

Hi kush!

I tried something like that:

SALES:

LOAD 

        Product,

        Color,

        Outlet,

        Quantity,

        Value,

        Cost,

        ...

FROM

$(vStorePath)SALES.qvd (qvd)

WHERE( (DATE>= '01/01/2015' And DATE<= '01/01/2017'))

;

OUTER JOIN(SALES)

STOCK:

LOAD

    Product,

    Color,

    Outlet,

    Stock

And not work, so I created a key with(Product + Color + Outlet) and named KEY, to solve. But doens't work too.

[Kushal_Chawda · Accepted solution]

Make sure that values on which you are joining having same values in both the tables.

SALES:

LOAD 

        capitalize(trim(Product)) as Product,

        capitalize(trim(Color)) as Color,

        capitalize(trim(Outlet)) as Outlet,

        Quantity,

        Value,

        Cost,

        ...                  

FROM

$(vStorePath)SALES.qvd (qvd)

WHERE( (DATE>=date#( '01/01/2015','DD/MM/YYYY') And DATE<= date#('01/01/2017','DD/MM/YYYY'))

;

OUTER JOIN(SALES)

STOCK:

LOAD

        capitalize(trim(Product)) as Product,

        capitalize(trim(Color)) as Color,

        capitalize(trim(Outlet)) as Outlet

         Stock