9 Replies Latest reply on Dec 27, 2013 2:43 AM by Bhramar Chandrakar

    DATEPARSE in 8.1

    Jeff Lefeld

      I have updated to Tableau 8.1 and conected to a SAP HANA connection with multiple dates as strings or integers. I wanted to utilize DATEPARSE to convert these values into Dates within 8.1, but it doesn't recognize DATEPARSE as a funciton.

       

      Thanks,

       

      Jeff

        • 1. Re: DATEPARSE in 8.1
          Alex Kerin

          Dateparse is only available for certain datasources. You are back to using left, right, mid etc.

          1 of 1 people found this helpful
          • 2. Re: DATEPARSE in 8.1
            Jeff Lefeld

            Thanks Alex.

             

            I have tried to convert a Date string from SAP that is formatted as yyyymmdd and I am getting the following error.

             

            " All Fields must be aggregate or constant when using table calculations or fields from multiple data sources"

             

            Hopefully you are able to see the picture below where I only have 1 data source and these are not table calculations. If you have a solution without me having to strip all of my data and attaching the workbook that would be great. If not I am still an inexperienced tableau user and would need some instructions on the best steps to strip out my other data fields.

             

            Thanks!! 

             

            Date Calculation.png

            • 3. Re: DATEPARSE in 8.1
              Jonathan Drummey

              Thanks for the screenshot, it really shows everything possible. This looks to me like it should work and not get that error. My only thoughts are that maybe the workbook is corrupted or Tableau is confused, or there's some more obvious bug. Try creating a brand new workbook, connect to the data source, and create that field, if that works then you might still log this with Tableau tech support. If that doesn't doesn't work, then definitely contact Tableau tech support. They will probably want your log files, see this KB article for how to send them: http://kb.tableausoftware.com/articles/knowledgebase/sending-tableau-desktop-log-files

               

              Jonathan

              1 of 1 people found this helpful
              • 4. Re: DATEPARSE in 8.1
                Jeff Lefeld

                Thanks Jonathan,

                 

                I connected a brand new workbook and tried the calculation and it gave me the same error.

                • 5. Re: DATEPARSE in 8.1
                  Alex Kerin

                  Agreed, this looks like a bug

                  • 6. Re: DATEPARSE in 8.1
                    Robert Morton

                    Hi Jeff,

                     

                    I have determined that this problem only exists with Analytic Views in SAP HANA connections. Please do contact Support (support@tableausoftware.com) to report this problem.

                     

                    In the meantime, I'll explain why you're getting that strange error and what you may be able to do to work around it.

                     

                    First, I'll point out that the error seems to imply something about table calculations or data blending, even though the calculation clearly does not involve either. The reason for this is how Tableau supports certain calculated field functions such as 'MID' once all the data has been brought locally in to memory for processing by the Tableau data pipeline. Since operations like table calculations and data blending occur within Tableau and not as part of the database, we must provide calculated field functions that support the same behavior as the remote database but are evaluated locally. Such locally evaluated functions depend on the data having first been aggregated, as you would expect to find with table calculations or data blending. That is why you see the error message, since you're attempting to use a local function (which isn't at all distinguishable from the supported remote database functions) but you are referencing fields that are not part of an aggregate expression.

                     

                    Here's the workaround: Wrap all instances of your dimension inside an ATTR() aggregation. The result should look like:

                    date(

                    mid(ATTR([POSTING_DATE_SAP_T]), 5, 2)

                    +"/"+right(ATTR([POSTING_DATE_SAP_T]), 2)

                    +"/"+left(ATTR([POSTING_DATE_SAP_T]), 4))

                    You will then need to place the field [POSTING_DATE_SAP_T] on the 'Level of Detail' shelf.

                     

                    Workaround explained: A certain type of aggregation in Tableau is called 'ATTR', and it's not strictly limited to use with data blending. The ATTR() aggregation will make a dimension look like an aggregate expression, so using ATTR will convince Tableau that the results have been aggregated and can be evaluated locally with functions like 'MID'. Note that ATTR will produce '*' values (a special type of NULL) whenever you are aggregating at a level coarser than the dimension you have within ATTR. However when your granularity is at the same or finer granularity than the dimension you are aggregating, the ATTR aggregation will preserve all of the data from the dimension and will not produce '*'. This is why I recommended placing the dimension in your level of detail shelf. For those who are curious, ATTR simply evaluates to the following formula: ATTR([dim]) := IIF( MIN([dim]) = MAX([dim], MIN([dim]), NULL).

                     

                    I hope this helps,

                    Robert

                    • 7. Re: DATEPARSE in 8.1
                      Jonathan Drummey

                      Hi Robert,

                       

                      I'm getting around to reading this and trying to make sense of your explanation...are you saying that SAP HANA doesn't support MID() or that there's something not working somewhere between HANA and Tableau, so Tableau is falling back on its own implementation of MID() that requires aggregation, and then generating the error agg/non-agg error?

                       

                      I think maybe my confusion stems from the Tableau Data Engine being able to perform row-level calculations, so I've perhaps been assuming that that ability would be available to other data sources, but that doesn't appear to be the case. If that's true, that would help explain the number of seeming inconstencies in function results across data sources and aggregation levels that I've encountered, including SIGN, STR, ROUND, and DATE functions.

                       

                      Jonathan

                      • 8. Re: DATEPARSE in 8.1
                        Robert Morton

                        Hi Jonathan,

                         

                        Yes, in this particular case the SAP HANA connection is powered by what SAP calls an Analytic View, which has limited functionality compared to a relational SAP HANA connection.

                         

                        When Tableau falls back to its own implementation (in this case, for MID), it relies on the Viz Engine. This engine was unveiled at TCC12 and primarily showed off our new rendering capabilities, but the number crunching that the Viz Engine performs goes far beyond the functionality needed for rendering. The Viz Engine also handles relational queries against cached query results; functions that must be computed locally, such as MID in this thread; and all parts of our data pipeline performed on the query result set, including table calculations, data blending, totals, reference lines, etc.

                         

                        Note that I didn't mention the Data Engine. It is a separate technology from the Viz Engine, and is not related to the issues discussed in this thread.

                         

                        -Robert

                        • 9. Re: DATEPARSE in 8.1
                          Bhramar Chandrakar

                          Hi Jeff,

                          How did you manage to connect to SAP HANA. I am trying to connect Tableau to HANA from a very long time but i am unable to find the way out. Even, i am not getting the exact name of the HANA drivers to download.So,Can you please show me the way to get out of it??