3 Replies Latest reply: Jan 2, 2017 8:21 AM by Sunny Talwar RSS

    Two Period date for One Line Chart

    juan prado

      Hi!

       

      I need some help for this. I have the following tables

       

      ClientProcess IDBill DateBill Money
      10001-0

      58

      Jul - 05 - 2016200
      10111-22Jun - 17 - 2016300
      30451-21May - 25 -2016150
      10111-23jul - 17 - 2016300

       

      ClientProcess IDCollection DateCollection Money
      10001-0

      58

      Jul - 25 - 2016200
      10111-22Jul - 01 - 2016300
      30451-21Jun - 10 -2016150
      10111-23jul - 27 - 2016300

       

      I need to make a table where i can see if the Client have been billed and Collected. If the money collected was the total for the Billed Money.

       

      I think that if I concatenate CLIENT  with PROCESS ID maybe i can relate the tables. Please help me with this!

       

      Thanks you!

        • Re: Two Period date for One Line Chart
          Sunny Talwar

          Why don't you join them instead of Concatenating them:

           

          Table:

          LOAD Client,

                    [Process ID],

                    [Bill Date],

                    [Bill Money]

          FROM .....;

           

          Join (Table)

          LOAD Client,

                    [Process ID],

                    [Collection Date],

                    [Collection Money]

          FROM .....;

           

          This will put them side by side making the overall comparison easy to do.

            • Re: Two Period date for One Line Chart
              sravanthi aluvala

              But it will form synthetic keys using Client and process ID.

              Sunny T <span class="icon-status-icon icon-mvp" title="Mvp"></span> wrote:

               

              Why don't you join them instead of Concatenating them:

               

              Table:

              LOAD Client,

                        [Process ID],

                        [Bill Date],

                        [Bill Money]

              FROM .....;

               

              Join (Table)

              LOAD Client,

                        [Process ID],

                        [Collection Date],

                        [Collection Money]

              FROM .....;

               

              This will put them side by side making the overall comparison easy to do.

               

              Try this

              table1:

              LOAD  Client&' '&[Process ID] as clientprocessid,

                    Client,

                   [Process ID],

                   [Bill Date],

                   [Bill Money]

              FROM

              [D:\Excel\Period date.xls]

              (biff, embedded labels, table is Sheet1$);

              table2:

              LOAD Client&' '&[Process ID] as clientprocessid,

                   [Collection Date],

                   [Collection Money]

              FROM

              [D:\Excel\Period date.xls]

              (biff, embedded labels, table is Sheet2$);

               

              img123.png