1 Reply Latest reply on Jul 13, 2018 2:30 PM by Joshua Milligan

    calculation based on 2 files

    Novita Rogers

      I have one file with expense amounts, each amount has a corresponding expense code, and the second file has expense codes and fringe percentage rates (not all expense codes in the first file have fringe percentage rates). I need to calculate a new total amount column based on   [firstfile.expense amount] *(1+ [secondfile.fringe rate]). How can I create a new calculated field in the first file by pulling the fringe rate from the second file?



        • 1. Re: calculation based on 2 files
          Joshua Milligan

          Hi Novita,


          I'd first join the two files together where the type codes matched.  But I'd make it a left join so that I kept all Expenditures, even if there wasn't a matching Rate Code:



          You can see here, that I'll keep all 9 expenditures in the Expenditure file, even though only one rate code matched.


          Then, I'd add a Step to do the calculation:


          The calculation in Tableau Prep would be something like:

          [Actual Expenditure Amount] * (1 + ZN([Rate]))



          Which is similar to what you have above, but I"ve included a ZN() function to turn any NULL values (where the lookup didn't work) to 0 so the result is the original amount * 1 = the original amount.


          I've attached a packaged flow with sample data that you can download and check out.


          Best Regards,


          1 of 1 people found this helpful