Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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;
Forgot to say that the first table "Articles" is a load from excel file,
the other "ArticleInfo" is a SQL query...
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
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.
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;
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)
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;
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;