5 Replies Latest reply on Jul 1, 2013 6:42 AM by Luis Amadeo

    Creating a calculated field using two different data sets.

    Luis Amadeo

      This might be a good question for Jonathan. I have two different data sets. One is for hospital incidents such as falls, medication errors, etc. and in the other one we track when a patient is restrained for some reason. I want to be able to produce a report that will show when a patient has an episode of restraint within x amount of time from an incident such as a fall, etc. Ideally, I would like to produce a detail report showing the date, time, patient, episode and, if within x amount of time, restraint date and time. Also, it would be helpful to produce a report aggregating this data. I can't post data due to privacy issues, but each data sets consist of date, MR#, type of incident, etc. The restraint data set has similar fields. Thanks in advance for any help.

        • 1. Re: Creating a calculated field using two different data sets.
          Luis Amadeo

          Sorry, I can't post the workbook as it has protected health information. Let me see if I can copy the structure and put some dummy data on it.

          • 2. Re: Creating a calculated field using two different data sets.
            Shawn Wallwork

            Luis please read #1 carefully:

             

            mock-up.png

            Shawn

            • 3. Re: Creating a calculated field using two different data sets.
              Jonathan Drummey

              Hi Luis,

               

              I think I can give you some ideas without requiring you to put together some anonymized data.

               

              To do this sort of analysis in Tableau you need to have a record set with the all the dimensional values that you'd need in the view (i.e. on Rows, Columns, Pages, or Marks Card), which in this case would be at least the Date/Time and MRN. There are a couple of ways to go about this:

               

              - Create a scaffold data source that has all the combinations, use that as the primary data source, then use Tableau Data Blending to add your two secondary data sources. There are a variety of ways to go about this, from doing it all in queries in the underlying source, to Custom SQL, to a bit of custom query combined with Tableau's domain padding.

              - Union your two data sources together.

               

              Cheers,

               

              Jonathan

              • 4. Re: Creating a calculated field using two different data sets.
                Luis Amadeo

                Thanks Jonathan, I got it and I am creating the scaffold source by dumping both data sources into an excel table. If I understand this correctly, then I should join this with the two original data sources through data blending.

                 

                Now, the part that I am hanging is how to create the filter or calculated field to show those records of patients that had a restraint episode within 2-8 hrs. of a fall episode (for example). I am not that good on nested IFF statements.

                 

                 

                Thanks,

                 

                Luis

                • 5. Re: Creating a calculated field using two different data sets.
                  Jonathan Drummey

                  If you have all of the fields from both data sources in your scaffold source, then you wouldn't necessarily need a data blend, because you'd have all the data.

                   

                  Doing the comparison across rows of the data definitely gets complicated. I use two different approaches:

                   

                  - If the problem area is sufficiently generalizable (like identifying patients with a primary or secondary diagnosis of COPD), then I'll add a subquery to underlying data source that adds one or more columns to the Tableau data source to flag the necessary condition(s). Though this can take more work to set up, it can be easier to maintain and more performant than table calcs.

                   

                  - Use Tableau table calcs to generate a flag. Given an Episode Type and DateTime, you can use something like:

                  IF MIN([Episode Type]) == "Fall" AND LOOKUP(MIN([Episode Type],-1) == "Restraint" AND DATEDIFF('hour',MIN([Episode DateTime]),LOOKUP(MIN([Episode DateTime]),-1)) <= 8 THEN

                       "Restraint w/in 8 hours"

                  END

                   

                  This would need a unique identifer of each episode as part of the addressing (Compute Using), and be partitioned on each patient.

                   

                  Jonathan