14 Replies Latest reply: Nov 28, 2014 10:31 AM by O'Brian Newell RSS

    Issue with Joins and Calculated Columns

    O'Brian Newell

      Hello,

       

      I have a problem that I have been trying to solve for the past 2 or so days.

      I have 3 tables:

                [table1_rates]:

              

                [table2_data1]:

                [table3_data2]:

       

      The data tables have the same headings, so I need to join them together and and look the rates from the rates table to return the rate and then do a currency conversion.

           This is my script so far:

       

      [table1_rates]:

      LOAD *

      FROM table1_rates;

      [table2_data1]:
      LOAD*,
      (AMOUNT/RATE) AS CONVERSION;
      LOAD*,
      LOOKUP('Rate','CCYDATE',CCYDATE1,'table2_rates') as RATE;
      LOAD*
      FROM [table2_data1];

      JOIN LOAD*,
      (AMOUNT/RATE) AS CONVERSION;
      LOAD*,
      LOOKUP('Rate','CCYDATE',CCYDATE1,'table2_rates) as RATE;
      LOAD*
      FROM [table2_data2];

       

           I have also tried concatenating the tables together but the data keeps exploding and return more rows than it should.

       

           Any help would be much appreciated.

        • Re: Issue with Joins and Calculated Columns
          Marcus Sommer

          I think it should look more like this:

           

          [table1_rates]:

          LOAD * FROM table1_rates;

          [data]:
          LOAD

               *,
              
          LOOKUP('Rate','CCYDATE',CCYDATE1,'table1_rates')/RATE AS CONVERSION,

               LOOKUP('Rate','CCYDATE',CCYDATE1,'table1_rates')
          FROM [table2_data1];

          concatenate (data)


          LOAD

               *,
              
          LOOKUP('Rate','CCYDATE',CCYDATE1,'table1_rates')/RATE AS CONVERSION,

               LOOKUP('Rate','CCYDATE',CCYDATE1,'table1_rates')
          FROM [table2_data2];

           

          - Marcus

            • Re: Issue with Joins and Calculated Columns
              O'Brian Newell

              Hi Marcus,

               

              Thanks for your reply.

              It doesn't seem to like that.

               

              Do you know any information about the load order and how this affects the execution of the script?

               

              I have used multiple tabs for each part of the script.

                • Re: Issue with Joins and Calculated Columns
                  Marcus Sommer

                  The main load-order is from top to bottom and from left to right and you could only access data with functions which are already loaded. Exceptions are preceding loads which loads from bottom to top and most often you would create wrong data if the load order are not properly.

                   

                  - Marcus

                    • Re: Issue with Joins and Calculated Columns
                      O'Brian Newell

                      Ok cool, so in that case I could:

                       

                      [table1_rates]

                      LOAD*FROM rates;

                      [table1]:

                      LOAD NAME AS ID

                           NUM(DATE) AS DATE,

                           FIELDNAME AS CCY,

                           (CCY&DATE) AS CCYDATE,

                           LOOKUP ('Rate','CCYDATE',CCYDATE,'tables_rates') AS RATE,

                           AMOUNT/RATE AS CONVERSION

                      FROM   tables1;

                       

                      CONCATENATE (table1)

                      LOAD NAME AS ID

                           NUM(DATE) AS DATE,

                           FIELDNAME AS CCY,

                           (CCY&DATE) AS CCYDATE,

                           LOOKUP ('Rate','CCYDATE',CCYDATE,'tables_rates') AS RATE,

                           AMOUNT/RATE AS CONVERSION

                      FROM   tables1;

                        • Re: Issue with Joins and Calculated Columns
                          Marcus Sommer

                          The concatenate-table had the same source like the load above - I assume this is a typo. But your access to renamed fields won't work in this way, instead:

                           

                          (CCY&DATE) AS CCYDATE,

                          LOOKUP ('Rate','CCYDATE',CCYDATE,'tables_rates') AS RATE,

                           

                          you need

                           

                          (FIELDNAME&DATE) AS CCYDATE,

                          LOOKUP ('Rate','CCYDATE',FIELDNAME&DATE,'tables_rates') AS RATE,

                           

                          - Marcus

                          • Re: Issue with Joins and Calculated Columns
                            O'Brian Newell

                            I have tried the putting data in a top-down approach but it keeps returning field not found even though the field was already loaded


                              • Re: Issue with Joins and Calculated Columns
                                Marcus Sommer

                                How looks your real load-script?

                                  • Re: Issue with Joins and Calculated Columns
                                    O'Brian Newell

                                    I have tested and the script runs without any error the only problem is the rate is not being returned.

                                    My script is:

                                         RATES:

                                         Rate, CCYDATE

                                         FROM Rates;

                                     

                                         TABLE1:

                                         LOAD TRADE_DATE,

                                         NUM(TRADE_DATE) AS DATE,

                                         CURRENCY AS CCY,

                                         AMOUNT,

                                         (CURRENCY&TRADE_DATE) AS CCYDATE1,

                                         LOOKUP('Rate','CCYDATE',CURRENCY&TRADE_DATE,'RATES') AS RATE,

                                         AMOUNT/LOOKUP('Rate','CCYDATE',CURRENCY&TRADE_DATE,'RATES') AS CONVERTED

                                         FROM TABLE1;

                                     

                                        

                                         CONCATENATE (TABLE1)

                                         LOAD TRADE_DATE,

                                         NUM(TRADE_DATE) AS DATE,

                                         CURRENCY AS CCY,

                                         AMOUNT,

                                         (CURRENCY&TRADE_DATE) AS CCYDATE1,

                                         LOOKUP('Rate','CCYDATE',CURRENCY&TRADE_DATE,'RATES') AS RATE,

                                         AMOUNT/LOOKUP('Rate','CCYDATE',CURRENCY&TRADE_DATE,'RATES') AS CONVERTED

                                         FROM TABLE2;

                                    • Re: Issue with Joins and Calculated Columns
                                      O'Brian Newell

                                      I think I have found a solution.

                                      Instead of:

                                            TABLE1:

                                           LOAD TRADE_DATE,

                                           NUM(TRADE_DATE) AS DATE,

                                           CURRENCY AS CCY,

                                           AMOUNT,

                                           (CURRENCY&TRADE_DATE) AS CCYDATE1,

                                           LOOKUP('Rate','CCYDATE',CURRENCY&TRADE_DATE,'RATES') AS RATE,

                                           AMOUNT/LOOKUP('Rate','CCYDATE',CURRENCY&TRADE_DATE,'RATES') AS CONVERTED

                                           FROM TABLE1;

                                       

                                          

                                           CONCATENATE (TABLE1)

                                           LOAD TRADE_DATE,

                                           NUM(TRADE_DATE) AS DATE,

                                           CURRENCY AS CCY,

                                           AMOUNT,

                                           (CURRENCY&TRADE_DATE) AS CCYDATE1,

                                           LOOKUP('Rate','CCYDATE',CURRENCY&TRADE_DATE,'RATES') AS RATE,

                                           AMOUNT/LOOKUP('Rate','CCYDATE',CURRENCY&TRADE_DATE,'RATES') AS CONVERTED

                                           FROM TABLE2;

                                      I have done this:

                                       

                                          TABLE1:

                                           LOAD TRADE_DATE,

                                           CURRENCY,

                                           AMOUNT,

                                           (CURRENCY&TRADE_DATE) AS CCYDATE1,

                                           LOOKUP('Rate','CCYDATE',CURRENCY&NUM(TRADE_DATE),'RATES') AS RATE,

                                           AMOUNT/LOOKUP('Rate','CCYDATE',CURRENCY&NUM(TRADE_DATE),'RATES') AS CONVERTED

                                           FROM TABLE1;

                                       

                                          

                                           CONCATENATE (TABLE1)

                                           LOAD TRADE_DATE,

                                           NUM(TRADE_DATE) AS DATE,

                                           CURRENCY AS CCY,

                                           AMOUNT,

                                           (CURRENCY&TRADE_DATE) AS CCYDATE1,

                                          LOOKUP('Rate','CCYDATE',CURRENCY&NUM(TRADE_DATE),'RATES') AS RATE,

                                           AMOUNT/LOOKUP('Rate','CCYDATE',CURRENCY&NUM(TRADE_DATE),'RATES') AS CONVERTED

                                           FROM TABLE2;

                                       

                                      And it seems to do the trick.

                          • Re: Issue with Joins and Calculated Columns
                            O'Brian Newell

                            I think the problem is the concate between CURRENCY AND TRADE_DATE

                            Because if the concate can be executed properly then the lookup should return the rate and allow for the conversion