7 Replies Latest reply on Nov 5, 2013 12:16 AM by Ramon Martinez

# Linking Primary Data Source to Secondary Reference Table - Data Blend

Note: I posted this question several days ago but got no replies....I'm on a tight deadline so needed to repost to get a response.  If Joe Mako and/or Jonathan Drummey see this...please help!!

Hi All,

I've attached a sample packaged workbook with a Data Blending Problem I need to fix.  I have two data sources, one is the primary data source which has a count of hospital discharges and a count of 30 day readmissions by Month and by Hospital. I also calculate a readmission rate (readmits/discharges).  What I need to do is link the count of hospital discharges, which is a measure, to a reference table (secondary data source) which is a replication of the Students T Table with a Critical T Stat value for every Degree of Freedom.  Essentially, what I've done is create another calculated field called "Total Discharges (N-1) Degrees of Freedom" in my primary data source.  All this does is take the total sum of hospital discharges and subtracts 1 from it.  I want to link (data blend) this measure to the "Degrees of Freedom" dimension in the Secondary Data Source to grab the Critical T Stat Measure and attach it to the table I've created.  Long story short, I'm trying to calculate 95% confidence intervals using the Critical T Stat.  If I'm able to grab the Critical T Stat then I can create a calculated field to calculate the Confidence Interval of a Proportion which is a pretty easy calculation but thus far I can't seem to be able to do this data blend to grab the Critical T Stat and attach it to my Total Discharges (N-1) Degrees of Freedom measure in the Primary Data Source.

So when you open up the packaged workbook, you'll see the first row and column, "Central Zone Hospital 1 for April 2003" with Total Discharges = 1008 and Total Discharges (N-1) Degrees of Freedom = 1007. In the secondary data source (Students T Table) the Critical T Stat would be = 1.9623225064.

If I can get this to work, in addition I would need a type of case statement that defaults the Critical T Stat to 1.96 if the Total Discharges (N-1) Degrees of Freedom measure is > 500,001 because I have 500,000 degrees of freedom possible in my table.

Hope this makes sense. I'm using Tableau 7 at present.  Any help or recommendations would be much appreciated.

Thanks,

Ed

• ###### 1. Re: Linking Primary Data Source to Secondary Reference Table - Data Blend

Hi Ed,

I see a fundamental problem here. Data blending is done at data level between two data sources. According to the scenario you've described, It seems you want to blend aggregated measures in a table view with a data source (secondary source).

According to my understanding that is not possible.

We need to figure out other approach to get the critical value from a secondary source.

Best regards,

Ramon

1 of 1 people found this helpful
• ###### 2. Re: Linking Primary Data Source to Secondary Reference Table - Data Blend

Thanks Ramon. Problem is that depending on the rollup or drilldown...the Total Discharges will change depending on whether you are rolled up to the Hospital Zone or drill down to the Hospital.  I have to grab the Critical T Stat using the aggregated (summed) up value.  Can we not use some type of lookup perhaps?  Anything that works I'm willing to go with.

• ###### 3. Re: Linking Primary Data Source to Secondary Reference Table - Data Blend

Hi Ed,

I've thinking in a solution but without success yet.

Ramon

• ###### 4. Re: Linking Primary Data Source to Secondary Reference Table - Data Blend

Hi Ed,

What Ramon said is accurate, data blending won't work using an aggregate measure as a linking field. Sometimes we can create a view that flips the blend around (and pads out the data with all the necessary dimensions), but given you have 500K Degrees of Freedom and there's still the aggregate measure on the other side of the blend to deal with I don't think that'll work.

Here are the options I can think of:

- Pre-aggregate your data and do the lookup in Custom SQL or a custom query/view. You may need to do two pre-aggregations for the different aggregation levels (Hospital Zone or Hospital).

- Depending on what precision you need for the Critical T Stat, maybe you could use less precision and generate a massive CASE statement on the Discharges Degrees of Freedom with a couple of hundred values (Tableau does have a size limit on calculated fields that I've run into, though).

- Build the equation that generates the Critical T Stat inside Tableau, I'm not sure that's really possible given that it seems to be a recursive algorithm. You might be able to make a good enough approximation of it.

- Wait for Tableau v8.1 to be released and upgrade, then get the Critical T Stat from R. The R integration in v8.1 operates as a table calculation (i.e. after aggregations are computed), so I'm almost 100% certain it could get you the results you want and handle drill up/down.

Jonathan

PS: You can ping people inside a post (and generate an email to them) by typing an @ symbol and then the person's name, like Scott Tennican who might be interested in this thread. (Type slowly because it takes a little while for the Jive forum software to catch up). You can also set up a ping by using the Insert Link button at the top of the post editor.

1 of 1 people found this helpful
• ###### 5. Re: Linking Primary Data Source to Secondary Reference Table - Data Blend

Hi Ed,

You can typically compute the critical value of hypothesis tests using Table Calcs in Tableau. However, since Tableau doesn't provide access to a student-t distribution, you either need to build a table or write a Table Calc which approximates the p-value for your critical value.

However, in v8.1 you could use the R t.test function to compute your critical value and the p-value to go with it.

Tyler Martin has converted my example tTest workbook t-test of two independent means to one which uses custom R script Table Calcs instead. I've attached it.

Scott

1 of 1 people found this helpful
• ###### 6. Re: Linking Primary Data Source to Secondary Reference Table - Data Blend

Thanks Ramon, Jonathan and Scott....your input is much appreciated.

Scott - thanks for the attachment...I'll have a look and see I can use any of this for my example.

Thanks again guys

Ed

• ###### 7. Re: Linking Primary Data Source to Secondary Reference Table - Data Blend

Hi Ed,