6 Replies Latest reply on Aug 1, 2012 1:58 PM by Richard Leeke

    RAWSQL_INT not correctly aware of the WHERE clause?

    Patrick Geuder

      I'm trying to make use of the RAWSQL-funcations in Tableau 6.1, when I run into some peculiarities.

       

      Please excuse the nonsense queries, but they serve a learning purpose ;-)

       

      In Tabnleau I've created a Calculated Field holding:

       

      RAWSQL_INT("SELECT MAX(game_id) FROM game_player_stats WHERE game_player_stats.game_id=%1", [game_id])

       

      When placing this on a row shalf on an empty sheet I get a list of game ids and my calculation:

       

      game_id     Calculation1

      ...

      12               7446

      70               7446

      ...

       

      The expected result was:

       

      game_id     Calculation1

      ...

      12               12

      70               70

      ...

       

      When running the query in a regular SQL client with the parameters hard coded I get the expected result:

       

      SELECT MAX(game_id) FROM scrolls.game_player_stats WHERE game_player_stats.game_id=12;

       

      MAX(game_id)

      12

       

       

      It seems like the MAX(game_id) is ignoring the WHERE clause and gives the MAX game_id in the whole table, which is 7446.

      Any why to make it aware of the clause?

       

       

      Any tips much appreciated,

       

      /Geuder

        • 1. Re: RAWSQL_INT not correctly aware of the WHERE clause?
          Richard Leeke

          I think you probably want RAWSQLAGG_INT().

           

          Have a look in the Tableau log file (My Documents\My Tableau Repository\Logs\log.txt) to see the SQL that Tableau is generating.

          • 2. Re: RAWSQL_INT not correctly aware of the WHERE clause?
            Patrick Geuder

            When looking where you point me i find both a .log file and .log-files with appending numbers. The last updated is .log2 but it contains only SQL when the query failed.

             

            I can make the RAWSQL-query to fail on the basis of table names, would such a dump be of use?

             

            SELECT (SELECT MAX(game_id) FROM game_player_statss WHERE game_player_stats.game_id=`game_player_stats`.`game_id`) AS `none_Calculation_7710801094957009_ok`, `game_player_stats`.`game_id` AS `none_game_id_ok`, COUNT(DISTINCT `game_player_stats`.`game_id`) AS `usr_Unique_Profile (copy)_qk`

            FROM `game_player_stats`

            GROUP BY 1, 2

             

             

             

            -----.Log2----

            2012-08-01 14:07:25.429 (103c): DATA INTERPRETER: Executing primary query.

            2012-08-01 14:07:25.442 (0c28): <QUERY protocol='0c2817a0'>

            2012-08-01 14:07:25.442 (0c28): SELECT (SELECT MAX(game_id) FROM game_player_statss WHERE game_player_stats.game_id=`game_player_stats`.`game_id`) AS `none_Calculation_7710801094957009_ok`,

            2012-08-01 14:07:25.442 (0c28):   `game_player_stats`.`game_id` AS `none_game_id_ok`,

            2012-08-01 14:07:25.442 (0c28):   COUNT(DISTINCT `game_player_stats`.`game_id`) AS `usr_Unique_Profile (copy)_qk`

            2012-08-01 14:07:25.442 (0c28): FROM `game_player_stats`

            2012-08-01 14:07:25.442 (0c28): GROUP BY 1,

            2012-08-01 14:07:25.442 (0c28):   2

            2012-08-01 14:07:25.442 (0c28): </QUERY>

            2012-08-01 14:07:25.552 (0c28):

            2012-08-01 14:07:25.552 (0c28): --- COM Error

            • 3. Re: RAWSQL_INT not correctly aware of the WHERE clause?
              Patrick Geuder

              Sorry, that query included some additional aggregations.

               

              SELECT (SELECT MAX(game_id) FROM game_player_stats

              WHERE game_player_stats.game_id=`game_player_stats`.`game_id`) AS `none_Calculation_7710801094957009_ok`, `game_player_stats`.`game_id` AS `none_game_id_ok`

              FROM `game_player_stats`

              GROUP BY 1, 2

               

               

              I'm surprised that: WHERE game_player_stats.game_id=`game_player_stats`.`game_id`)

              doesn't hold anything suggesting that the query is using the current row's game_id.

               

              ---

              Basically, what I'm looking to do in the end (not this specific query) is to

              1) for each row in the DB,

              2) find other rows in the DB that have the same game_id

              3) check if any one of them have a specific profile name

              4) if so tag this row accordingly (if match => X or not match => Y.

               

              The way I want to do that is to add a field through a calculated field that takes the current row's game_id and does a RAWSQL that looks if there are other rows that have the same game_id AND have this specific profile.

               

              Hope this helps you see what I'm after.

              • 4. Re: RAWSQL_INT not correctly aware of the WHERE clause?
                Robert Morton

                Hi Patrick,

                 

                RAWSQL calculated fields enable users to write expressions using the SQL syntax of their database, but you cannot use RAWSQL to write entire clauses. Such clauses include SELECT, WHERE, HAVING, etc. Instead, the legal expressions for RAWSQL are those which can only appear within a clause.

                 

                As an alternative, I suggest you look at creating a Custom SQL connection since it allows you to define any SQL query you desire so long as it returns a result set.

                 

                Last, it appears that you're doing a form of cohort analysis, and I'll point out that this can be a challenge for any system that works with relational data. Fortunately there are several techniques that can be used in Tableau, including Table Calculations and Ad-hoc Groups. You may wish to familiarize yourself with those features, as well as any resources on the web for explaining general techniques for cohort analysis. We also have a Knowledge Base article describing cohort analysis with cohorts grouped by date/time, which you may be able to translate into your situation of grouping cohorts by [game_id]; here's the article: http://kb.tableausoftware.com/articles/knowledgebase/cohort-analysis-tips

                 

                I hope this helps,

                Robert

                1 of 1 people found this helpful
                • 5. Re: RAWSQL_INT not correctly aware of the WHERE clause?
                  Patrick Geuder

                  Thanks, yes I'll probably be able to solve this issue by creating a Custom SQL connection and extending the columns fetched through a self-join. I still think it would be immensely powerful to have the possibility to create attributes through additional queries including WHERE clauses based on what row currently is being process.

                   

                  Is there any good place where I can read up on the RAWSQL functions, preferably with examples.

                   

                  (Also, Thanks for the cohort tip article. This was not cohorts, but that will be very helpful for other projects.)

                  • 6. Re: RAWSQL_INT not correctly aware of the WHERE clause?
                    Richard Leeke

                    You can use RAWSQL to do correlated sub-queries in the way that you were thinking of - just as long as the expression returns a single-value (ie one column for one row).

                     

                    I've attached a "Superstore Sales" example showing how to find the maximum unit price of any of the line items on an order and include that on every order line (I can't think why you'd want to do that specifically, but it shows the technique: correlating by the order id in this case).

                     

                    As Robert says there are other ways to do that sort of thing in Tableau like table calculations - though depending on the size of your database there are times when doing things in the SQL will scale further, so it's good to have all the tricks in your kit bag. (The example I've attached is using the MS JET engine which does this sort of thing very slowly, though.)