Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

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
Honored Contributor II

Re: Beginner join problem

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;

7 Replies
Not applicable

Re: Beginner join problem

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

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

jonbrough
Valued Contributor

Re: Beginner join problem

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
Honored Contributor II

Re: Beginner join problem

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.

Re: Beginner join problem

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

Re: Beginner join problem

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
Honored Contributor II

Re: Beginner join problem

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;

Re: Beginner join problem

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;

Community Browser