4 Replies Latest reply: Mar 18, 2015 6:13 PM by Erik Mendieta RSS

    Join problem

      Hi

       

      I want to join two tables, and insert the new calculate data on the first one, look at the example:


      Table1:

      MeasureValueDate
      Units1.001
      Dollar25.001
      Units1.002
      Dollar25.002

       

      Table2:

      DateRate
      11.05
      21.09

       

      I would like to include on the Table 1, this lines that means (Dollar: Value * Rate on the same Date)

       

      MeasureValueDate
      LocalValue26,251
      LocalValue27,252

       

      I'm try to do like this, but I continue have a problem:

       

      JOIN (Table2)

      LOAD

        'LocalValue' as [Measure],

        [Value]*[Rate] AS [Value]

      RESIDENT Table1

      WHERE [Measure] = 'Dollar'

       

      but I'm geting this error message:

      Error Field not found - <Rate>

       

      What I'm doing wrong?

      --- EDITED

      Example here:

       

      Table1:

      Load * inline

      [

      Measure,Value,Date

      Units,1,1

      Dollar,25,1

      Units,1,2

      Dollar,25,2

      ];

       

      Table2:

      Load * inline

      [

      Date,Rate

      1,2

      2,3

      ];

       

      Table1:

      JOIN (Table2)

      LOAD

        'LocalValue' as [Measure],

        [Value]*[Rate] AS [Value]

      RESIDENT Table1

      WHERE [Measure] = 'Dollar'

        • Re: Join problem
          Peter Rieper

          Not quite clear on the difference in line 1 and 2 resp. 3 and 4 in the first table. Please explain.

           

          If there is a 1:1 ratio, would recomment to use a mapping:

          mapRate: MAPPING LOAD Date, Rate FROM ....

           

          Data:

          LOAD

               *,

              Value * APPLYMAP('mapRate', Date)          AS Local

          FROM

               ....;

           

          If there are multiple currencies involved, you may consider a combined key.

          Else check on the INTERVALMATCH-function

           

          HTH Peter

          • Re: Join problem
            Sunny Talwar

            May this:

             

            Table1:

            Load * inline

            [

            Measure,Value,Date

            Units,1,1

            Dollar,25,1

            Units,1,2

            Dollar,25,2

            ];

             

            Table2:

            Load * inline

            [

            Date,Rate

            1,2

            2,3

            ];

             

            Join (Table1)

            LOAD

              'Dollar' as [Measure],

              Date,

               Rate

            RESIDENT Table2;

             

            Table:

            LOAD *,

              Value * Rate as [Total Value]

            Resident Table1;

             

            DROP Tables Table1, Table2;

             

            Output:

             

             

            Attaching the sample application for ease of understanding.

             

            HTH

             

            Best,

            S

            • Re: Join problem
              Massimo Grossi

              1.png

               

               

              Table1:

              Load * inline [

              Measure,Value,Date

              Units,1,1

              Dollar,25,1

              Units,1,2

              Dollar,25,2

              ];

               

              Table2:

              Load * inline [

              Date,Rate

              1,1.05

              2,1.09

              ];

               

              left join (Table2) load

              'LocalValue' as Measure,

              Value,

              Date

              Resident Table1

              Where Measure = 'Dollar';

               

              Concatenate (Table1)

              load Measure, Value*Rate as Value, Date

              Resident Table2;

               

              DROP Table Table2;

              • Re: Join problem
                Erik Mendieta

                Hugs