-
1. Re: Sql Query with calculations drops records on extract
Robert MortonJan 15, 2013 5:35 PM (in response to Nicole Le Prohn)
1 of 1 people found this helpfulHi Nicole,
This is an interesting case, and I'd like you to contact our excellent support team (support@tableausoftware.com) in order to gather more information from you.
In the meantime, here's my take on the possible problem. First, Tableau is attempting to optimize those calculations by materializing their results, which is done by querying the database for all fields plus all calculations that should be materialized. The database may have no trouble handling the case where DISCHARGEDATE is null, but it may be giving the driver a NULL of type DATE instead of a NULL of type FLOAT, which is the expected result type for your calculation. When the driver hits the first row of data that has this problematic value, it bombs and prevents Tableau from completing the extract of the entire set of rows.
As a workaround, try the following formula for EXITAGE. I've written it to test my assertion that the problem is with null data types. Please let me know if that addresses the issue for you. Here's the formula:
EXITAGE= IIF(ISNULL(DISCHARGEDATE), FLOAT(NULL), FLOAT(DISCHARGEDATE - DOB)/365.25)
I hope this helps,
Robert
-
2. Re: Sql Query with calculations drops records on extract
Nicole Le Prohn Jan 16, 2013 3:19 PM (in response to Robert Morton)Hi Robert,
Thanks for the quick reply. I will contact the support team on Friday when I'm back in the office.
Your formula worked - although as I noted above I also found a formula that worked. What I'm most currious about is why the orginal formula did not work for this extract, but has worked for all the other extracts I've done with the same data elements. Perhaps the support team will have some answers.
Nicole
-
3. Re: Sql Query with calculations drops records on extract
Robert MortonJan 16, 2013 3:45 PM (in response to Nicole Le Prohn)
Hi Nicole,
The only guess I have is that the conditions are different enough that we may not attempt to materialize that same calculation when used in other connections with extracts. You may be able to confirm this by looking at the queries issued to the database to pull data required to build the extract. If the calculated field is materialized, its formula will be compiled to SQL and issued to the database when populating the extract. The connections which do not exhibit this problem during extract creation likely never materialize the calculated fields in question.
-Robert