2 Replies Latest reply on Jul 20, 2018 8:13 AM by jonathan kassak

    Calculated field using data elements from primary and secondary data sources

    jonathan kassak

      Presently, I have a calculated field with all fields from one data
      source.  The primary data source is named “Pathway Variance Extract”.  My calculation is checking to see if an orthopedic
      patient undergoing a certain orthopedic procedure (e.g., a
      rapid recovery procedure for a total knee replacement) met certain criteria for
      BMI, Age and time they entered the recovery room.  Patients in this pathway should have the lowest length of stay at the hospital.

       

      The calculation is as follows and seems to work just fine:

       

      (if ([BMI] <= 35 and [PAT_AGE] <= 80 and [RR part 1]
      <= 15)

      then "Criteria
      Met"

      else "Inclusion Criteria NOT Met" END)

       

      By the way, "RR part 1" is the hour the patient entered the recovery room.

       

      I would like to pull in an additional data element into the
      above calculation, which is an element named  “anesthesia type”.  This data is resident in a secondary data source extract
      called “OR_Extract”.  This extract is linked to the “Pathway Variance Extract”. I am fairly certain I have joined correctly and that is not my question.

       

      I tried something like the following, but I’m not getting an
      acceptable calculation.

       

      if ([BMI] <= 35 and [PAT_AGE] <= 80 and [RR part 1]
      <= 15) and [OR_Extract].[ANES_TYPE_NM] = "neuraxial
      block"
      then
      "Criteria Met" else "Inclusion Criteria NOT Met" END)

       

      Requesting a look at my syntax or perhaps
      suggest another option(s) for doing this.

       

      My objective is to see if all criteria were met for:

      BMI (from Pathway Variance Extract)

      Age (from Pathway Variance Extract)

      Recovery Room Time (from Pathway Variance Extract)

      Anesthesia Type (from OR_Extract)

       

      I am using Tableau 10.3

      Thanks in advance for any options or suggestions !!!!

        • 1. Re: Calculated field using data elements from primary and secondary data sources
          Esther Aller

          Hi Jonathan,

           

          When creating calculations with fields from multiple data connections, all of the fields must be aggregated. For example:

           

          IF SUM([BMI]) <= 35

          AND SUM([PAT_AGE]) <= 80

          AND SUM([RR part 1]) <= 15

          AND MIN([OR_Extract].[ANES_TYPE_NM]) = "neuraxial block"

          THEN Criteria Met"

          ELSE "Inclusion Criteria NOT Met"

          END

           

          The issue you might encounter with the above calculation is that the aggregated fields may not return the value needed to evaluate the conditions. For example, if every row in the Tableau view is a hospital room, and there are 2 patients in a room (one aged 35 and the other 60), then SUM([PAT_AGE]) will be 95. The calculation will return "Inclusion Criteria NOT Met" for the entire room.

           

          Thus, if you use the above calculation, you want to make sure your view has enough detail (aka every row should be a patient).

           

           

          Another option would be to use cross-database joins rather than data blending. Data blending is an ad-hoc LEFT join where data from the secondary data is aggregated up to the level of detail in the view. This is why calculations with fields from a secondary data source must use aggregations.

           

          Cross-database joins are a record level join, which means you can use fields from any table as normal. For more information about cross-database joins, see Combine tables from different databases

           

          Hope this answers your question

          Combine tables from different databases

          1 of 1 people found this helpful
          • 2. Re: Calculated field using data elements from primary and secondary data sources
            jonathan kassak

            Thank you so much for taking the time to review this and for providing me a way to make the calculation work.

            It was the perfect Friday morning gift for me to receive.

            Very much appreciated.   This worked perfectly for me.

            Have a great weekend.