topic Re: Beginner join problem in QlikView

Beginner join problem

Wed, 17 Oct 2012 12:21:58 GMT

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:

Article	WeightClass	Stock	PriceClass
10001	2	10	5
1000201	1	5	1
1000202	5	4	3
1000203	2	5	2
10003	1	5	1
1000401	5	8	2
1000402	8	5	4
10005	5	5	2

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:

Article	WeightClass	Stock	PriceClass
1000201	1	5	1
1000202	5	4	3
1000203	2	5	2
1000401	5	8	2
1000402	8	5	4
10005	5	5	2

Any help is appreciatied.

Thanks in advance.

Re: Beginner join problem

Wed, 17 Oct 2012 12:30:05 GMT

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

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

Re: Beginner join problem

Anonymous — Wed, 17 Oct 2012 12:34:48 GMT

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

Re: Beginner join problem

giakoum — Wed, 17 Oct 2012 12:36:53 GMT

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

CELAMBARASAN — Wed, 17 Oct 2012 12:54:25 GMT

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;

Re: Beginner join problem

Wed, 17 Oct 2012 13:58:17 GMT

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)

Re: Beginner join problem

giakoum — Wed, 17 Oct 2012 14:07:45 GMT

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

CELAMBARASAN — Wed, 17 Oct 2012 14:08:53 GMT

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;