topic Re: Difficult with JOIN! in QlikView

Difficult with JOIN!

Wed, 25 Nov 2020 16:16:04 GMT

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!

Re: Difficult with JOIN!

sunny_talwar — Wed, 30 Mar 2016 11:22:16 GMT

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

Re: Difficult with JOIN!

Anonymous — Wed, 30 Mar 2016 11:26:04 GMT

use the left keep may be get the output

Re: Difficult with JOIN!

Kushal_Chawda — Wed, 30 Mar 2016 11:29:18 GMT

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

Re: Difficult with JOIN!

Wed, 30 Mar 2016 11:34:07 GMT

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.

Re: Difficult with JOIN!

Wed, 30 Mar 2016 11:40:18 GMT

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.

Re: Difficult with JOIN!

Kushal_Chawda — Wed, 30 Mar 2016 14:38:53 GMT

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