11 Replies Latest reply: Apr 2, 2017 3:11 AM by Massimo Grossi RSS

    creating a new table

    Ronit Kanfi

      Hello :-)

       

      I have two tables with invoice data:

      Invoice:

      LOAD

        IV,

        PART, //PAYPART

      QUANT as PAYQUANT

      InvoiceDetailed:

      LOAD

          IV,

          PART, //ACTUALPART

          QUANT as ACTUALQUANT

      for example:

      Invoice table:

      IV  PART    PAYQUANT

      19    XXX   15

      19    ZZZ    13

      InvoiceDetailed table:

      IV    PART    ACTUALQUANT

      19    XXX    15

      19    YYY    22


      What I need is to have a final table with all Possibilities:

      IV    PART    PAYQUANT    ACTUALQUANT

      19     XXX     15                    15

      19     ZZZ      13                     0

      19     YYY      0                      22

       

      I would appreiciate your help in finding a solution.

        • Re: creating a new table
          Anil Babu Samineni

          What is the tabular you are getting by using Join?

           

          [Invoice table]:

          LOAD * Inline [

          IV,  PART   , PAYQUANT

          19,    XXX ,  15

          19,    ZZZ,    13

          ];

          Join

          [InvoiceDetailed table]:

          LOAD * Inline [

          IV ,   PART  ,  ACTUALQUANT

          19  ,  XXX ,   15

          19   , YYY,    22

          ];

          • Re: creating a new table
            Ronit Kanfi

            you can see that i have two keys - IV and APRT.

            Therefore join does not work.

            I have also made table of keys, and it didn't work as well since there are different shows in table invoice and table  InvoiceDetailed, So the result os always only the parts from invoice.

            • Re: creating a new table
              Massimo Grossi

              maybe you have a different name for PART field in the 2 source tables, PAYPART and ACTUALPART;

              you should rename PAYPART as PART and ACTUALPART as PART if you want the join on 2 fields: the join work if the fields has the same names

               

              [Invoice Table]:

              LOAD

                  IV,                                        // join field

                  PAYPART as PART,              // join field     

                  QUANT as PAYQUANT;

              SQL SELECT

                  IV,

                  PAYPART,

                  QUANT

              FROM

                  .............

                  ;

               

              JOIN ([Invoice Table])

              LOAD

                  IV,                                            // join field

                  ACTUALPART as PART,            // join field

                  QUANT as ACTUALQUANT

              SQL SELECT

                  IV,

                  ACTUALPART,

                  QUANT

              FROM

                  .............

                  ;