Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Outer Join Tables from MySQL Database on load.

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_idmanufacturer_id
1a
2b
3c

has first of all to merge with table: seo_product

table "seo_product"

product_idmanufacturer_id
4a
5b
6c

( 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_idmanufacturer_name
abrand1
bbrand2
cbrand3

table "category"

product_idcategory_id
1a
2b
3c

and table "landingpage"

product_idlandingpage_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

2 Replies

Re: Outer Join Tables from MySQL Database on load.

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

Not applicable

Re: Outer Join Tables from MySQL Database on load.

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);