5 Replies Latest reply: Jul 1, 2014 4:54 AM by Ravichandra Nadiminti RSS

    Problems to make join into two tables

      Hi,

       

      First I apologize for my english.

       

      I have to join two tables and i don't know what join I have to use.

       

      I need all registers of the first table and all registers of the second table that don't be in the first table. If there are repeated that are in both tables, I need only the register that is in the first table.

       

      Attach my code:

       

      PURCHS:

      LOAD

        TEXT(PURCHID) & '-' & TEXT(LINENUM)                as PURCHLINE,

        TEXT(INVENTDIMID)                                               as INVENTDIMID,

        TEXT(DIMENSION3_)                                              as PROJECT,

        NUM(PURCHPRICE)                                                 as PURCHPRICE,

        TEXT(CURRENCYCODE)                                         as CURRENCYCODE,

        DATE(CONFIRMEDDLV)                                          as CONFIRMEDDLV,

        NUM(QTYORDERED)                                               as QTYORDERED

      FROM

      [$(Ruta_QVD)\PURCHLINE.qvd] (qvd)

      WHERE DATAAREAID = 'tase' ;

      JOIN

      LOAD

        TEXT(PURCHID) & '-' & TEXT(LINENUM)                   as PURCHLINE,

        TEXT(INVENTDIMID)                                                  as INVENTDIMID,

        TEXT(DIMENSION3_)                                                 as PROJECT,

        NUM(PURCHPRICE)                                                    as PURCHPRICE,

        TEXT(CURRENCYCODE)                                            as CURRENCYCODE,

        DATE(CONFIRMEDDLV)                                              as CONFIRMEDDLV,

        NUM(QTYORDERED)                                                   as QTYORDERED

      FROM

      [$(Rute_QVD)\PURCHLINE_2003.qvd] (qvd)

      WHERE DATAAREAID = 'aeo' ;

       

      this code sometimes returns two registers with the same purchline, for example, it happens if the project or another attribute is different in both tables. I only would need the register of the first table. How can I do it?

       

      Thanks in advance.

        • Re: Problems to make join into two tables
          sasi k

          HI

          simply concatenate both the tables

          LOAD

            TEXT(PURCHID) & '-' & TEXT(LINENUM)                as PURCHLINE,

            TEXT(INVENTDIMID)                                               as INVENTDIMID,

            TEXT(DIMENSION3_)                                              as PROJECT,

            NUM(PURCHPRICE)                                                 as PURCHPRICE,

            TEXT(CURRENCYCODE)                                         as CURRENCYCODE,

            DATE(CONFIRMEDDLV)                                          as CONFIRMEDDLV,

            NUM(QTYORDERED)                                               as QTYORDERED

          FROM

          [$(Ruta_QVD)\PURCHLINE.qvd] (qvd)

          WHERE DATAAREAID = 'tase' ;

          concatineate

          LOAD

            TEXT(PURCHID) & '-' & TEXT(LINENUM)                   as PURCHLINE,

            TEXT(INVENTDIMID)                                                  as INVENTDIMID,

            TEXT(DIMENSION3_)                                                 as PROJECT,

            NUM(PURCHPRICE)                                                    as PURCHPRICE,

            TEXT(CURRENCYCODE)                                            as CURRENCYCODE,

            DATE(CONFIRMEDDLV)                                              as CONFIRMEDDLV,

            NUM(QTYORDERED)                                                   as QTYORDERED

          FROM

          [$(Rute_QVD)\PURCHLINE_2003.qvd] (qvd)

          WHERE DATAAREAID = 'aeo' ;

          • Re: Problems to make join into two tables
            Ravichandra Nadiminti

            Hi,

            follow this pattern..You will get the required output

             

            C:

            load * Inline
            [
            cname,sales
            a,100
            b,200
            c,400
            a,500
            ]
            ;
            Concatenate
            load * Inline
            [
            cname,sales
            a,100
            b,200
            c,400
            a,500
            d,600
            ]
            ;

            load Distinct *
            Resident C;

             

            HTH,

            Ravi N.