2 Replies Latest reply: Apr 28, 2016 9:30 AM by Chris Snyders RSS

    Applying a field to 2 other fields

    Andrew Thomas

      Hi

       

      I have the following table

       

       

      Join(MarginData)
      LOAD if([Local Currency] = 'CNY', 'RMB', [Local Currency]) As Currency,
      [Conversion Factor]
      FROM
      [D:\SourceDocuments\PRODQLIK\DATA\SQL\FX_RATE_CALENDAR.qvd]
      (
      qvd)
      Where CalendarDate = Date(Today(),'YYYY-MM-DD');

       

      I'm joining this to a table called MarginData

       

      IN the MrginData table I have 2 fields

       

      1. LocalCurrency

      2. DocumentCurrency

      Each record in the table will have both local and document currency fields e.g GBP and EUR

       

      The first table contain the exchange rates for USD for all currencies and I need to be able to apply the exchange rate to both fields and for it to select the right currency

       

      e.g

       

      Record 1  - Local currency GBP= 100  Doc currency EUR = 110

       

      USD rate GBP to USD = 1.40

      USD rate EUR to USD = 1.35

       

      I need the record to show

       

      Local currency in USD $140 Doc Currency in EUR = 148.50

       

        • Re: Applying a field to 2 other fields
          Sunny Talwar

          I think instead of doing a left join, consider doing a mapping load here:

           

          MappingTable:

          Mapping

          LOAD if([Local Currency] = 'CNY', 'RMB', [Local Currency]) As Currency,
          [Conversion Factor]
          FROM
          [D:\SourceDocuments\PRODQLIK\DATA\SQL\FX_RATE_CALENDAR.qvd]
          (qvd)
          Where CalendarDate = Date(Today(),'YYYY-MM-DD');


          MarginData:

          LOAD AllYourOtherStuff,

                    ApplyMap('MappingTable', LocalCurrency, Null()) as LocalCurrencyRate,

                    ApplyMap('MappingTable', DocumentCurrency, Null()) as DocumentCurrencyRate

          FROM/Resident .....


          UPDATE: I have used a false condition within the ApplyMap as Null(), but you might want to add another value such as 1 (The part in Green Above)

          • Re: Applying a field to 2 other fields
            Chris Snyders

            Hi Andrew

            Can you maybe share an example of the 2 tables with a couple of rows?