topic Re: Outer Join Tables from MySQL Database on load. in QlikView

Outer Join Tables from MySQL Database on load.

Sun, 07 Apr 2013 18:55:49 GMT

Hey everyone, i have been working all sunday through explanation on how to outer join tables. But i can not get it to work.

Thats where i am coming from:

table "product"

product_id	manufacturer_id
1	a
2	b
3	c

has first of all to merge with table: seo_product

table "seo_product"

product_id	manufacturer_id
4	a
5	b
6	c

( the product ids are unique in both tables so there is no ovelapp.

After this two tables are joined i want them to join with the following

has to join with:

table "manufacturer"

manufacturer_id	manufacturer_name
a	brand1
b	brand2
c	brand3

table "category"

product_id	category_id
1	a
2	b
3	c

and table "landingpage"

product_id	landingpage_url
1	/x
2	/y
3	/z

The whole data comes from a mysql database. I am sorry, to bother anyone, but i really couldnt get it to work.

I need some starting point what to do after the

ODBC CONNECT TO database;

...

Thanks in advance

Re: Outer Join Tables from MySQL Database on load.

kaushiknsolanki — Mon, 08 Apr 2013 06:32:36 GMT

Hi,

Try below Code.

Sql Select a.Product_ID,a.Manufacturer_ID,b.Manufacturer_Name,c.Category_ID,d.LandingPage_URL

From Product a, Manufacturer b, Category c, landingpage d

Where a.Manufacturer_ID = b.Manufacturer_ID and

a.Product_ID = c.Product_ID and

a.Product_ID = d.Product_ID

Union All

Sql Select a.Product_ID,a.Manufacturer_ID,b.Manufacturer_Name,c.Category_ID,d.LandingPage_URL

From seo_product a, Manufacturer b, Category c, landingpage d

Where a.Manufacturer_ID = b.Manufacturer_ID and

a.Product_ID = c.Product_ID and

a.Product_ID = d.Product_ID

Regards,

Kaushik Solanki

Re: Outer Join Tables from MySQL Database on load.

Mon, 08 Apr 2013 09:19:05 GMT

Hi chrisgerber,

First you have to concatenate the two first tables, not join them (Concatenate is like Union and Join is like Intersection). And then join the rest of tables, like this (I have attached an example with Excel data):

LOAD product_id,

manufacturer_id

FROM

TestData.xlsx

(ooxml, embedded labels, table is Product);

Concatenate // --> This table has to be concatenated, not joined (Concatenate is like Union, Join like Intersection)

LOAD product_id,

manufacturer_id

FROM

TestData.xlsx

(ooxml, embedded labels, table is SEOProduct);

Join

LOAD manufacturer_id,

manufacturer_name

FROM

TestData.xlsx

(ooxml, embedded labels, table is Manufacturer);

Join

LOAD product_id,

category_id

FROM

TestData.xlsx

(ooxml, embedded labels, table is Category);

Join

LOAD product_id,

landingpage_url

FROM

TestData.xlsx

(ooxml, embedded labels, table is LandingPage);