7 Replies Latest reply: Oct 17, 2012 10:08 AM by Celambarasan Adhimulam RSS

    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.

        • 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...

            • 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

              • Re: Beginner join problem
                ioannis giakoumakis

                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 Adhimulam

                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

                    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
                        ioannis giakoumakis

                        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 Adhimulam

                          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;