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?

       

      2018-07-13_13-59-41.jpg

        • 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,

          Joshua

          1 of 1 people found this helpful