3 Replies Latest reply on Jan 16, 2013 3:45 PM by Robert Morton

    Sql Query with calculations drops records on extract

    Nicole Le Prohn
    Nicole Le Prohn Jan 15, 2013 4:36 PM

      I have a SQL query that pulls 14,815 records into Tabelau. I have about 20 calculations I add to the workbook for this query. I can extract the data just fine without the calculations. When I extracted the data with the calculations, it only extracted 1426 records. There is no discernable pattern as to why it drops the records.

       

      After much testing, I discovered that if I include the following calculated variables (either one), Tabelau only extracts 1426 records.

       

      EXITAGE = (DISCHARGEDATE - DOB)/365.25  - note, DISCHARGEDATE may be blank,

      INTAKEAGE = INT((ADMITDATE - DOB)/365.25) - No fields are blank

       

      What is extra weird is that it does just fine with this calculation

      AGE = (

      IF ISNULL([DISCHARGEDATE]) THEN INT((TODAY() - [DOB])/365.25)

      ELSE INT((DISCHARGEDATE - DOB)/365.25)

      END)

       

      So, I tried this - which worked.

      DISCHARGEDATE = (IF ([DISCHARGEDATE])  < TODAY() THEN  ((DISCHARGEDATE - DOB)/365.25)

      END)

       

      Any reason why one crashes the extract and the other doesn't? To further complicate things, I use these same calcuations for other extracts and they work perfectly!

      • 164 Views
      • Categories: Calculations, Extracts
      • Tags:
        • 1. Re: Sql Query with calculations drops records on extract
          Robert Morton
          Robert Morton Employee Jan 15, 2013 5:35 PM (in response to Nicole Le Prohn)

          Hi 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

          1 of 1 people found this helpful
          • 2. Re: Sql Query with calculations drops records on extract
            Nicole Le Prohn
            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 Morton
              Robert Morton Employee Jan 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