Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Beginner join problem

Hi!

I´m having problems when joining two tables...

The first table holds a list of articles like:

Articles:

Article
10002
10004
10005

and so on...

the other table holds inoformation about thesse articles

ArticleInfo:

ArticleWeightClassStockPriceClass
100012105
1000201 151
1000202543
1000203252
10003151
1000401582
1000402854
10005552

As you can see article 10002 has an extension '01','02','03' because this article is divided into differernt pieces.

So, how do I join theese so that I can create a result table that looks like this:

ArticleWeightClassStockPriceClass
1000201 151
1000202543
1000203252
1000401582
1000402854
10005552

Any help is appreciatied.

Thanks in advance.

1 Solution

Accepted Solutions
giakoum
Partner - Master II
Partner - Master II

Articles:

LOAD @1 AS Article,

FROM

C:\NewArticles.xls

(biff, no labels, header is 1 lines, table is [Data$], filters(

Remove(Col, Pos(Top, 1), Remove(Col, Pos(Bottom, 2))

));

ODBC CONNECT TO ********************

ArticleInfo:

Load

    Left(Article,5) As Article,

    Article as FullArticle,

    WeightClass,

    Stock,

    PriceClass

where

     exists(Article);

SQL SELECT * FROM ArticleInfo;

View solution in original post

7 Replies
Not applicable
Author

Forgot to say that the first table "Articles" is a load from excel file,

the other "ArticleInfo" is a SQL query...

Anonymous
Not applicable
Author

Can't see the need for a join, as Articles only has a field Article that is in the ArticleInfo table.

However, if there are more fields there then:

Left Join (Articles)

    Article,                                        // I am assuming Article is the only similarly named field on the two tables

    WeightClass,

    Stock,

    PriceClass

RESIDENT ArticleInfo:               // This is assuming you loaded Article Info into a Qlikview table first. If you don't want to then this RESIDENT line could be an SQL statement instead

Be careful that the Article only appears once in both tables, to ensure you don't get a cartesian join. You can do this by testing the number of rows of the Articles table (hovering over it's header in the Table Viewer will show what this is) before and after your join.

Jonathan

giakoum
Partner - Master II
Partner - Master II

i would load the Article from the excel first.

and then from SQL :

load

     left(Article,5) as Article,

     Article as FullArticle,

     .......

where

     exists(Article)

This way only records that exist in the excel Artcile field would come from SQL.

CELAMBARASAN
Partner - Champion
Partner - Champion

If your article number is always 5 chars? then you can go with this approach

Articles:

Load

     Article

from ExcelSource;

left join(Articles)

Load

    Left(Article,5) As Article,

   Article As DetailArticleNo,

   WeightClass,

   Stock,

   PriceClass;

SQL Query;

Not applicable
Author

Hi!

I´m confused... how does the load know which table to load from?

Do you mean something like this?

Articles:

LOAD @1 AS Article,

FROM

C:\NewArticles.xls

(biff, no labels, header is 1 lines, table is [Data$], filters(

Remove(Col, Pos(Top, 1), Remove(Col, Pos(Bottom, 2))

));

ODBC CONNECT TO ********************

left join(Articles)

Load

    Left(Article,5) As Article;

SELECT * FROM ArticleInfo

where

     exists(Article)

giakoum
Partner - Master II
Partner - Master II

Articles:

LOAD @1 AS Article,

FROM

C:\NewArticles.xls

(biff, no labels, header is 1 lines, table is [Data$], filters(

Remove(Col, Pos(Top, 1), Remove(Col, Pos(Bottom, 2))

));

ODBC CONNECT TO ********************

ArticleInfo:

Load

    Left(Article,5) As Article,

    Article as FullArticle,

    WeightClass,

    Stock,

    PriceClass

where

     exists(Article);

SQL SELECT * FROM ArticleInfo;

CELAMBARASAN
Partner - Champion
Partner - Champion

Articles:

LOAD @1 AS Article,

FROM

C:\NewArticles.xls

(biff, no labels, header is 1 lines, table is [Data$], filters(

Remove(Col, Pos(Top, 1), Remove(Col, Pos(Bottom, 2))

));

ODBC CONNECT TO ********************

left join(Articles)

Load

    Left(Article,5) As Article;

SELECT * FROM ArticleInfo;