5 Replies Latest reply: Dec 21, 2012 8:53 AM by Mark Houston RSS

    Calculating fields in load script

    Mark Houston

      I have loaded a table that looks like this...

      Master_General:
      LOAD
      [Employee ID] as EMP_ID,
      [Annual Salary Amount] as EMP_Salary
      [CURRENCY KEY] as KEY_Currency,
                
      FROM
      [Z:\Confidential\Mark\Metrics Dashboard 2\Dashboard Sources\General\Master General.xlsx]
      (ooxml, embedded labels, table is General);
      

       

      and another loaded table that looks like this...

      Exchange_Rates:
      LOAD
      [CURRENCY KEY] as KEY_Currency,
      [EXCHANGE RATE] as Exchange_Rate
      
      FROM
      [Z:\Confidential\Mark\Metrics Dashboard 2\Dashboard Sources\General\Master Exchange Rates.xlsx]
      (ooxml, embedded labels, table is Sheet1);
      

       

      I want to calculate a field in the load script like EMP_Salary * Exchange_Rate.  But I don't know the proper syntax.

      Do I use an INLINE load?  Do I need to use RESIDENT?  Do I need to create and then drop a temporary table? Do I need to have both tables load before I can complete these calculations?  Could someone please provide a simple example that illustrates and explains these things.  I can't figure them out from other posts.  Thanks.