7 Replies Latest reply on Jul 22, 2018 2:13 PM by Hau Hoang

    Lookup Question

    Hau Hoang

      Using a LOOKUP function or something similar, how do I obtain the desired "Lookup" values below?  If 'Currency 1' and 'Currency 2' match, then keep 'Currency 2 FX Rate'.  If no match, then obtain the foreign exchange rate of 'Currency 1' by looking up the foreign exchange rate of 'Currency 2'.  I also have a specific date selected and do not want to look up the foreign exchange rate from another date that exists in the database.

       

      Date

      ID

      Currency 1Currency 2Currency 2 FX RateLookup
      12/31/150548USDAUD0.7679001.000000
      12/31/152358USDCNY0.1589601.000000
      12/31/156657USDJPY0.0094091.000000
      12/31/154201JPYUSD1.0000000.009409
      12/31/151122USDCAD0.7755001.000000
      12/31/156096CADUSD1.0000000.775500
      12/31/151234CNYCNY0.1589600.158960
      12/31/153580USDKRW0.0094091.000000
      12/31/159197GBPGBP1.4015001.401500
      12/31/151011KRWUSD1.0000000.009409
      12/31/150215USDUSD1.0000001.000000
      12/31/154372AUDUSD1.0000000.767900

       

      Thanks in advance!

        • 1. Re: Lookup Question
          Ankit Bansal

          Hi Hua,

           

          You will need to do self join to achieve this. Create a copy of your source and join will original source on

          source1.currency1=source2.currency2 and source2.currency1=source1.currency2.

          you will have 2 columns now souce1.currency 2 FX Rate and souce2.currency 2 FX Rate .

          So now create a calculation column and put the condition:

          if source1.currency1=source1.currency2 then souce1.currency 2 FX Rate  else souce2.currency 2 FX Rate end.

           

          Also you may need to add date column also in join condition as you can have records for multiple dates.

          • 2. Re: Lookup Question
            Hau Hoang

            Hi Ankit,

             

            Thanks for responding.  Can you please elaborate on the first part of your response?  I copied the original source data to another source. 

             

            You will need to do self join to achieve this. Create a copy of your source and join will original source on

            source1.currency1=source2.currency2 and source2.currency1=source1.currency2.

            you will have 2 columns now souce1.currency 2 FX Rate and souce2.currency 2 FX Rate .

            So now create a calculation column and put the condition:

            if source1.currency1=source1.currency2 then souce1.currency 2 FX Rate  else souce2.currency 2 FX Rate end.

             

            If possible, can you share the workbook if you have it created?

             

            Thanks,

             

            Hau

            • 3. Re: Lookup Question
              Ankit Bansal

              Here it is. Please find attached.

              • 4. Re: Lookup Question
                Hau Hoang

                Thanks, Ankit.  It appears to work with the limited dataset I provided.  Is there an alternative without having to create a duplicate data source and joining?

                • 5. Re: Lookup Question
                  Ankit Bansal

                  Hua,

                   

                  It should work with large data set also. How many rows do you have in your actual data?

                  Also remember to join with date field also(along with 2 currency joins) like I have mentioned before else you will have cross join and data will bump up.

                   

                  Attaching the worksheet with Date also as join field. Use this on your full data.

                   

                  Thanks,

                  Ankit Bansal

                  • 6. Re: Lookup Question
                    Luis Chavez

                    Hi Hau,

                     

                    if it is a large dataset and the join lasts too long, you could create a new data source wich contains unique values (id - currency - rate), then make the join and use the same logic, maybe it would be useful.

                     

                    Regards

                    Luis

                    • 7. Re: Lookup Question
                      Hau Hoang

                      The data source I am using is managed by our company's IT resource and I am looking for a solution a business user could implement on a specific Tableau report without having the need to join sources.  Your answer does solve the issue but just not the way I had hoped for my particular circumstance.  Thanks again.