9 Replies Latest reply on Apr 22, 2019 1:12 PM by Eric Sammons

    TODAY() comparison is not behaving as expected

    Eric Sammons

      I have a Date field, YYYY-MM-DD and I'm trying to use an if condition as follows:

      {FIXED [Product]: COUNTD(IF [Customer Escalation] = True AND [Phase Date] = TODAY()
      THEN
      [Bug Id]
      END)}
      

       

      When I click OK a `Bad Connection: Tableau could not connect to the data source.` error is returned, which makes no sense.  I should add that the following works:

      {FIXED [Product]: COUNTD(IF [Customer Escalation] = True AND [Phase Date] = DATE('2019-04-18')
      THEN
      [Bug Id]
      END)}
      

       

      And removing the AND condition all together also works which tells me this issue is not a bad connection issue.

       

      The entire error message:

      An error occurred while communicating with data source 'rs_cstmr_tckt_to_test_case_close_loop (APL_VDB_ENG_GENERIC)'.

      Bad Connection: Tableau could not connect to the data source.

      com.tableausoftware.jdbc.TableauJDBCException: Exception in runQuery for query: SELECT "rs_cstmr_tckt_to_test_case_close_loop"."product" AS "product",

        COUNT(DISTINCT (CASE WHEN (("rs_cstmr_tckt_to_test_case_close_loop"."customer_escalation" <> 0) AND ("rs_cstmr_tckt_to_test_case_close_loop"."phase_date" = CAST(CURRENT_DATE AS DATE))) THEN "rs_cstmr_tckt_to_test_case_close_loop"."bug_id" ELSE NULL END)) AS "sum_Calculation_204350882660401152_ok",

        COUNT(DISTINCT (CASE WHEN (("rs_cstmr_tckt_to_test_case_close_loop"."customer_escalation" <> 0) AND ("rs_cstmr_tckt_to_test_case_close_loop"."has_test" <> 0)) THEN "rs_cstmr_tckt_to_test_case_close_loop"."bug_id" ELSE NULL END)) AS "sum_Calculation_204350882667360257_ok"

      FROM "APL_VDB_ENG_GENERIC"."rs_cstmr_tckt_to_test_case_close_loop" "rs_cstmr_tckt_to_test_case_close_loop"

      GROUP BY "rs_cstmr_tckt_to_test_case_close_loop"."product"

      Remote org.teiid.api.exception.query.QueryParserException: TEIID31100 Parsing error: Encountered "CAST(CURRENT_DATE [*]AS[*] DATE)" at line 2, column 177.

      Was expecting: "("

      SELECT "rs_cstmr_tckt_to_test_case_close_loop"."product" AS "product",

        COUNT(DISTINCT (CASE WHEN (("rs_cstmr_tckt_to_test_case_close_loop"."customer_escalation" <> 0) AND ("rs_cstmr_tckt_to_test_case_close_loop"."phase_date" = CAST(CURRENT_DATE AS DATE))) THEN "rs_cstmr_tckt_to_test_case_close_loop"."bug_id" ELSE NULL END)) AS "sum_Calculation_204350882660401152_ok",

        COUNT(DISTINCT (CASE WHEN (("rs_cstmr_tckt_to_test_case_close_loop"."customer_escalation" <> 0) AND ("rs_cstmr_tckt_to_test_case_close_loop"."has_test" <> 0)) THEN "rs_cstmr_tckt_to_test_case_close_loop"."bug_id" ELSE NULL END)) AS "sum_Calculation_204350882667360257_ok"

      FROM "APL_VDB_ENG_GENERIC"."rs_cstmr_tckt_to_test_case_close_loop" "rs_cstmr_tckt_to_test_case_close_loop"

      GROUP BY "rs_cstmr_tckt_to_test_case_close_loop"."product"

        • 1. Re: TODAY() comparison is not behaving as expected
          albert wong

          Hi Eric,

           

          Perhaps if you modify the date in the format pane for that field?

           

          • 2. Re: TODAY() comparison is not behaving as expected
            Eric Sammons

            For what I am trying to do that won't make a difference.  The date format feature tends to only be available if you are using date in your viz; however, in my case, I am not.  I'm simply trying to limit my countd() to those rows that have today's date (in the phase_date column).   I tried to find a way to format the date without placing date in my viz and couldn't find a way.

             

            For the moment I am trying with a parameter, but I don't know that this will work either.  I set the following values for my parameter.

             

            Name: MaxDate

            Date Type: Date

            Current Value: 4/18/2019  <- This i believe will be the problem.

            Display format: Automatic

            • 3. Re: TODAY() comparison is not behaving as expected
              Michel Caissie

              It looks like Tableau cannot cast Today() as a Date  when doing the query for the FIXED lod.

               

              You can try replacing today with 

              DATE(DATENAME('year',[today]) +'-'+ STR( DATEPART('month',[today]) ) +'-'+ DATENAME('day',[today] ) )

              This way you force the date to have the same format than what you have in your working scenario.

               

              Michel

              • 4. Re: TODAY() comparison is not behaving as expected
                Eric Sammons

                Thanks Michel Caissie for the response but I have one question.  The example you provide above uses [today] which is a reference to an unknown field, how would I use it in my situation where I'm trying to compare a field, phase_date to today()?

                • 5. Re: TODAY() comparison is not behaving as expected
                  Michel Caissie

                  ah,  it's just that I created a calculation for testing purposes. My  [today]  is actually   today() embedded in a calc.

                  You can try replacing   [today] with  today()

                  • 6. Re: TODAY() comparison is not behaving as expected
                    Eric Sammons

                    I tried both, just using today() and creating a measure today with today().  In both cases I get an error.  I also attempted type casting since you can't compare a date to a string so I used:

                    IF STR(Phase_Date) = (DATENAME('year', [today]) +'-'+ STR( DATEPART('month',[today]) ) +'-'+ DATENAME('day',[today] ) )

                    The Error

                    An error occurred while communicating with data source 'rs_cstmr_tckt_to_test_case_close_loop (APL_VDB_ENG_GENERIC)'.

                     

                    Bad Connection: Tableau could not connect to the data source.

                    com.tableausoftware.jdbc.TableauJDBCException: Exception in runQuery for query: SELECT "rs_cstmr_tckt_to_test_case_close_loop"."product" AS "product",

                      COUNT(DISTINCT (CASE WHEN (("rs_cstmr_tckt_to_test_case_close_loop"."customer_escalation" <> 0) AND (CAST("rs_cstmr_tckt_to_test_case_close_loop"."phase_date" AS VARCHAR(10)) = ((((CAST(CAST(EXTRACT(YEAR FROM CURRENT_DATE) AS INTEGER) AS VARCHAR(21)) || '-') || CAST(CAST(EXTRACT(MONTH FROM CURRENT_DATE) AS INTEGER) AS VARCHAR(21))) || '-') || CAST(CAST(EXTRACT(DAY FROM CURRENT_DATE) AS INTEGER) AS VARCHAR(21))))) THEN "rs_cstmr_tckt_to_test_case_close_loop"."bug_id" ELSE NULL END)) AS "sum_Calculation_204350882660401152_ok",

                      COUNT(DISTINCT (CASE WHEN (("rs_cstmr_tckt_to_test_case_close_loop"."customer_escalation" <> 0) AND ("rs_cstmr_tckt_to_test_case_close_loop"."has_test" <> 0)) THEN "rs_cstmr_tckt_to_test_case_close_loop"."bug_id" ELSE NULL END)) AS "sum_Calculation_204350882667360257_ok",

                      ((CASE WHEN COUNT(DISTINCT (CASE WHEN (("rs_cstmr_tckt_to_test_case_close_loop"."customer_escalation" <> 0) AND (CAST("rs_cstmr_tckt_to_test_case_close_loop"."phase_date" AS VARCHAR(10)) = ((((CAST(CAST(EXTRACT(YEAR FROM CURRENT_DATE) AS INTEGER) AS VARCHAR(21)) || '-') || CAST(CAST(EXTRACT(MONTH FROM CURRENT_DATE) AS INTEGER) AS VARCHAR(21))) || '-') || CAST(CAST(EXTRACT(DAY FROM CURRENT_DATE) AS INTEGER) AS VARCHAR(21))))) THEN "rs_cstmr_tckt_to_test_case_close_loop"."bug_id" ELSE NULL END)) = 0 THEN NULL ELSE CAST(COUNT(DISTINCT (CASE WHEN (("rs_cstmr_tckt_to_test_case_close_loop"."customer_escalation" <> 0) AND ("rs_cstmr_tckt_to_test_case_close_loop"."has_test" <> 0)) THEN "rs_cstmr_tckt_to_test_case_close_loop"."bug_id" ELSE NULL END)) AS FLOAT) / COUNT(DISTINCT (CASE WHEN (("rs_cstmr_tckt_to_test_case_close_loop"."customer_escalation" <> 0) AND (CAST("rs_cstmr_tckt_to_test_case_close_loop"."phase_date" AS VARCHAR(10)) = ((((CAST(CAST(EXTRACT(YEAR FROM CURRENT_DATE) AS INTEGER) AS VARCHAR(21)) || '-') || CAST(CAST(EXTRACT(MONTH FROM CURRENT_DATE) AS INTEGER) AS VARCHAR(21))) || '-') || CAST(CAST(EXTRACT(DAY FROM CURRENT_DATE) AS INTEGER) AS VARCHAR(21))))) THEN "rs_cstmr_tckt_to_test_case_close_loop"."bug_id" ELSE NULL END)) END) * 100) AS "sum_Calculation_204350882669629442_ok"

                    FROM "APL_VDB_ENG_GENERIC"."rs_cstmr_tckt_to_test_case_close_loop" "rs_cstmr_tckt_to_test_case_close_loop"

                    GROUP BY "rs_cstmr_tckt_to_test_case_close_loop"."product"

                    Remote org.teiid.api.exception.query.QueryParserException: TEIID31100 Parsing error: Encountered "YEAR FROM CURRENT_DATE[*])[*] AS INTEGER" at line 2, column 224.

                    Was expecting: "("

                    SELECT "rs_cstmr_tckt_to_test_case_close_loop"."product" AS "product",

                      COUNT(DISTINCT (CASE WHEN (("rs_cstmr_tckt_to_test_case_close_loop"."customer_escalation" <> 0) AND (CAST("rs_cstmr_tckt_to_test_case_close_loop"."phase_date" AS VARCHAR(10)) = ((((CAST(CAST(EXTRACT(YEAR FROM CURRENT_DATE) AS INTEGER) AS VARCHAR(21)) || '-') || CAST(CAST(EXTRACT(MONTH FROM CURRENT_DATE) AS INTEGER) AS VARCHAR(21))) || '-') || CAST(CAST(EXTRACT(DAY FROM CURRENT_DATE) AS INTEGER) AS VARCHAR(21))))) THEN "rs_cstmr_tckt_to_test_case_close_loop"."bug_id" ELSE NULL END)) AS "sum_Calculation_204350882660401152_ok",

                      COUNT(DISTINCT (CASE WHEN (("rs_cstmr_tckt_to_test_case_close_loop"."customer_escalation" <> 0) AND ("rs_cstmr_tckt_to_test_case_close_loop"."has_test" <> 0)) THEN "rs_cstmr_tckt_to_test_case_close_loop"."bug_id" ELSE NULL END)) AS "sum_Calculation_204350882667360257_ok",

                      ((CASE WHEN COUNT(DISTINCT (CASE WHEN (("rs_cstmr_tckt_to_test_case_close_loop"."customer_escalation" <> 0) AND (CAST("rs_cstmr_tckt_to_test_case_close_loop"."phase_date" AS VARCHAR(10)) = ((((CAST(CAST(EXTRACT(YEAR FROM CURRENT_DATE) AS INTEGER) AS VARCHAR(21)) || '-') || CAST(CAST(EXTRACT(MONTH FROM CURRENT_DATE) AS INTEGER) AS VARCHAR(21))) || '-') || CAST(CAST(EXTRACT(DAY FROM CURRENT_DATE) AS INTEGER) AS VARCHAR(21))))) THEN "rs_cstmr_tckt_to_test_case_close_loop"."bug_id" ELSE NULL END)) = 0 THEN NULL ELSE CAST(COUNT(DISTINCT (CASE WHEN (("rs_cstmr_tckt_to_test_case_close_loop"."customer_escalation" <> 0) AND ("rs_cstmr_tckt_to_test_case_close_loop"."has_test" <> 0)) THEN "rs_cstmr_tckt_to_test_case_close_loop"."bug_id" ELSE NULL END)) AS FLOAT) / COUNT(DISTINCT (CASE WHEN (("rs_cstmr_tckt_to_test_case_close_loop"."customer_escalation" <> 0) AND (CAST("rs_cstmr_tckt_to_test_case_close_loop"."phase_date" AS VARCHAR(10)) = ((((CAST(CAST(EXTRACT(YEAR FROM CURRENT_DATE) AS INTEGER) AS VARCHAR(21)) || '-') || CAST(CAST(EXTRACT(MONTH FROM CURRENT_DATE) AS INTEGER) AS VARCHAR(21))) || '-') || CAST(CAST(EXTRACT(DAY FROM CURRENT_DATE) AS INTEGER) AS VARCHAR(21))))) THEN "rs_cstmr_tckt_to_test_case_close_loop"."bug_id" ELSE NULL END)) END) * 100) AS "sum_Calculation_204350882669629442_ok"

                    FROM "APL_VDB_ENG_GENERIC"."rs_cstmr_tckt_to_test_case_close_loop" "rs_cstmr_tckt_to_test_case_close_loop"

                    GROUP BY "rs_cstmr_tckt_to_test_case_close_loop"."product"

                    • 7. Re: TODAY() comparison is not behaving as expected
                      Michel Caissie

                      When you say  I also attempted type casting since you can't compare a date to a string so I used

                       

                      Did you try  [Phase Date] = DATE(DATENAME('year',[today]) +'-'+ STR( DATEPART('month',[today]) ) +'-'+ DATENAME('day',[today] ) )

                       

                      because the Date() function  creates a date  with each String value of  Today().

                      So it is the closest to your working scenario   [Phase Date] = DATE('2019-04-18')

                       

                      Other than that I don`t know,  I would open a case with Tableau,  seems like a bug to me.

                      • 8. Re: TODAY() comparison is not behaving as expected
                        Eric Sammons

                        I thought I had because I got a calculation error that I could not compare a date to string, apparently, I did something wrong there because now I'm not getting that error.  I am; however, still getting the bad connection error.

                         

                        An error occurred while communicating with data source 'rs_cstmr_tckt_to_test_case_close_loop (APL_VDB_ENG_GENERIC)'.

                         

                        Bad Connection: Tableau could not connect to the data source.

                        com.tableausoftware.jdbc.TableauJDBCException: Exception in runQuery for query: SELECT "rs_cstmr_tckt_to_test_case_close_loop"."product" AS "product",

                          COUNT(DISTINCT (CASE WHEN (("rs_cstmr_tckt_to_test_case_close_loop"."customer_escalation" <> 0) AND ("rs_cstmr_tckt_to_test_case_close_loop"."phase_date" = CAST(((((CAST(CAST(EXTRACT(YEAR FROM CURRENT_DATE) AS INTEGER) AS VARCHAR(21)) || '-') || CAST(CAST(EXTRACT(MONTH FROM CURRENT_DATE) AS INTEGER) AS VARCHAR(21))) || '-') || CAST(CAST(EXTRACT(DAY FROM CURRENT_DATE) AS INTEGER) AS VARCHAR(21))) AS DATE))) THEN "rs_cstmr_tckt_to_test_case_close_loop"."bug_id" ELSE NULL END)) AS "sum_Calculation_204350882660401152_ok",

                          COUNT(DISTINCT (CASE WHEN (("rs_cstmr_tckt_to_test_case_close_loop"."customer_escalation" <> 0) AND ("rs_cstmr_tckt_to_test_case_close_loop"."has_test" <> 0)) THEN "rs_cstmr_tckt_to_test_case_close_loop"."bug_id" ELSE NULL END)) AS "sum_Calculation_204350882667360257_ok",

                          ((CASE WHEN COUNT(DISTINCT (CASE WHEN (("rs_cstmr_tckt_to_test_case_close_loop"."customer_escalation" <> 0) AND ("rs_cstmr_tckt_to_test_case_close_loop"."phase_date" = CAST(((((CAST(CAST(EXTRACT(YEAR FROM CURRENT_DATE) AS INTEGER) AS VARCHAR(21)) || '-') || CAST(CAST(EXTRACT(MONTH FROM CURRENT_DATE) AS INTEGER) AS VARCHAR(21))) || '-') || CAST(CAST(EXTRACT(DAY FROM CURRENT_DATE) AS INTEGER) AS VARCHAR(21))) AS DATE))) THEN "rs_cstmr_tckt_to_test_case_close_loop"."bug_id" ELSE NULL END)) = 0 THEN NULL ELSE CAST(COUNT(DISTINCT (CASE WHEN (("rs_cstmr_tckt_to_test_case_close_loop"."customer_escalation" <> 0) AND ("rs_cstmr_tckt_to_test_case_close_loop"."has_test" <> 0)) THEN "rs_cstmr_tckt_to_test_case_close_loop"."bug_id" ELSE NULL END)) AS FLOAT) / COUNT(DISTINCT (CASE WHEN (("rs_cstmr_tckt_to_test_case_close_loop"."customer_escalation" <> 0) AND ("rs_cstmr_tckt_to_test_case_close_loop"."phase_date" = CAST(((((CAST(CAST(EXTRACT(YEAR FROM CURRENT_DATE) AS INTEGER) AS VARCHAR(21)) || '-') || CAST(CAST(EXTRACT(MONTH FROM CURRENT_DATE) AS INTEGER) AS VARCHAR(21))) || '-') || CAST(CAST(EXTRACT(DAY FROM CURRENT_DATE) AS INTEGER) AS VARCHAR(21))) AS DATE))) THEN "rs_cstmr_tckt_to_test_case_close_loop"."bug_id" ELSE NULL END)) END) * 100) AS "sum_Calculation_204350882669629442_ok"

                        FROM "APL_VDB_ENG_GENERIC"."rs_cstmr_tckt_to_test_case_close_loop" "rs_cstmr_tckt_to_test_case_close_loop"

                        GROUP BY "rs_cstmr_tckt_to_test_case_close_loop"."product"

                        Remote org.teiid.api.exception.query.QueryParserException: TEIID31100 Parsing error: Encountered "YEAR FROM CURRENT_DATE[*])[*] AS INTEGER" at line 2, column 208.

                        Was expecting: "("

                        SELECT "rs_cstmr_tckt_to_test_case_close_loop"."product" AS "product",

                          COUNT(DISTINCT (CASE WHEN (("rs_cstmr_tckt_to_test_case_close_loop"."customer_escalation" <> 0) AND ("rs_cstmr_tckt_to_test_case_close_loop"."phase_date" = CAST(((((CAST(CAST(EXTRACT(YEAR FROM CURRENT_DATE) AS INTEGER) AS VARCHAR(21)) || '-') || CAST(CAST(EXTRACT(MONTH FROM CURRENT_DATE) AS INTEGER) AS VARCHAR(21))) || '-') || CAST(CAST(EXTRACT(DAY FROM CURRENT_DATE) AS INTEGER) AS VARCHAR(21))) AS DATE))) THEN "rs_cstmr_tckt_to_test_case_close_loop"."bug_id" ELSE NULL END)) AS "sum_Calculation_204350882660401152_ok",

                          COUNT(DISTINCT (CASE WHEN (("rs_cstmr_tckt_to_test_case_close_loop"."customer_escalation" <> 0) AND ("rs_cstmr_tckt_to_test_case_close_loop"."has_test" <> 0)) THEN "rs_cstmr_tckt_to_test_case_close_loop"."bug_id" ELSE NULL END)) AS "sum_Calculation_204350882667360257_ok",

                          ((CASE WHEN COUNT(DISTINCT (CASE WHEN (("rs_cstmr_tckt_to_test_case_close_loop"."customer_escalation" <> 0) AND ("rs_cstmr_tckt_to_test_case_close_loop"."phase_date" = CAST(((((CAST(CAST(EXTRACT(YEAR FROM CURRENT_DATE) AS INTEGER) AS VARCHAR(21)) || '-') || CAST(CAST(EXTRACT(MONTH FROM CURRENT_DATE) AS INTEGER) AS VARCHAR(21))) || '-') || CAST(CAST(EXTRACT(DAY FROM CURRENT_DATE) AS INTEGER) AS VARCHAR(21))) AS DATE))) THEN "rs_cstmr_tckt_to_test_case_close_loop"."bug_id" ELSE NULL END)) = 0 THEN NULL ELSE CAST(COUNT(DISTINCT (CASE WHEN (("rs_cstmr_tckt_to_test_case_close_loop"."customer_escalation" <> 0) AND ("rs_cstmr_tckt_to_test_case_close_loop"."has_test" <> 0)) THEN "rs_cstmr_tckt_to_test_case_close_loop"."bug_id" ELSE NULL END)) AS FLOAT) / COUNT(DISTINCT (CASE WHEN (("rs_cstmr_tckt_to_test_case_close_loop"."customer_escalation" <> 0) AND ("rs_cstmr_tckt_to_test_case_close_loop"."phase_date" = CAST(((((CAST(CAST(EXTRACT(YEAR FROM CURRENT_DATE) AS INTEGER) AS VARCHAR(21)) || '-') || CAST(CAST(EXTRACT(MONTH FROM CURRENT_DATE) AS INTEGER) AS VARCHAR(21))) || '-') || CAST(CAST(EXTRACT(DAY FROM CURRENT_DATE) AS INTEGER) AS VARCHAR(21))) AS DATE))) THEN "rs_cstmr_tckt_to_test_case_close_loop"."bug_id" ELSE NULL END)) END) * 100) AS "sum_Calculation_204350882669629442_ok"

                        FROM "APL_VDB_ENG_GENERIC"."rs_cstmr_tckt_to_test_case_close_loop" "rs_cstmr_tckt_to_test_case_close_loop"

                        GROUP BY "rs_cstmr_tckt_to_test_case_close_loop"."product"

                        • 9. Re: TODAY() comparison is not behaving as expected
                          Eric Sammons

                          Looks like this issue may be related to teiid.  Apparently date logic poses a problem when working with teiid, to work around the issue(s) I am using an extract and so far things are working much better.