2 Replies Latest reply on May 28, 2012 6:14 AM by Kester Harding

    How can I base calculations on values from a lookup table (secondary data source)?

    Kester Harding

      I am putting together a report that calculates the amount that we should bill based on a series of rules. The primary data source is taken from a SQL database. The secondary data source is from an Excel doc I created. These join on two fields: Worksource and Final Claim Type.


      I want to be able to use values from the 2nd source in calculated fields on the Primary source. In the attached workbook I've created a calculated field called 'Bill weighting'. I want it to look up a the field '50% billing rule' from the secondary source and then apply other calculations if it is yes, otherwise return a value of 1. It's not working because it's creating an aggregate but it doesn't need to because there is only ever one matching value in the secondary source for any given combination of worksource and claim type. How can I get Tableau to use this secondary source in the way that I want it to?


      The alternatives are to create a series of calculated fields with case...when statements to replace the lookup table (very messy and difficult to edit in future!) or to get the lookup table added to our database (have to convince IT to do it and then every time I edit it I will have to get them to do that as well.) Neither of these is really much good.


      Please help!