6 Replies Latest reply on Feb 10, 2013 12:17 PM by Andrew Watson

    Lookup field using conditions in SQL?

    Dave Ulliott

      Hi there!

      I was hoping somebody might be able to point me in the right direction with what, I believe, should be a fairly simple question.

      My company very recently purchased Tableau desktop, and I'm still getting my head around it, so apologies if this is a dumb question...

      What I am trying to accomplish is this:

      I am attempting to create a generated field using RAWSQL to lookup a user id in my data and return the date field associated with the record where a certain criteria is met.

       

      Something along the lines of :

       

      SELECT MIN(data.Event_Date)

      WHERE data.User_ID = data.User_ID

      AND       data.Event = "Install"

       

      I then want to be able plot out a different event ("Load") along a date axis, using a calculated field ([Load_date] - [calculated 'Install_date"]) in order to work out how many days since the install event

       

      I've searched through the forums and help files and found a few things that get me close, but not quite the whole way.

       

      Could anybody share their thoughts?

      Much appreciated

      Dave

        • 1. Re: Lookup field using conditions in SQL?
          Joshua Milligan

          Dave,

           

          It's not a dumb question at all.  It is a little bit difficult to answer without a some more context.  One thing that often helps is to post a packaged workbook

           

          (with dummy data if your data is sensitive.  Read http://community.tableau.com/docs/DOC-1251 and http://community.tableau.com/thread/121116).

           

          Screen shots are also nice (especially in this case, since you are dealing with RAWSQL).  Also, it would be helpful to know how close you are, what errors you are getting, or where exactly you are getting stuck.

           

          Welcome to the forums!

           

          Joshua

          • 2. Re: Lookup field using conditions in SQL?
            Andrew Watson

            Hi Dave

             

            As Joshua says it's hard to answer your question without more context but I'll make a suggestion that might help you anyway. Couldn't you create a calculated field to find the earliest install date? Something along the lines of MIN(IF event = 'install' THEN DateField END).

             

            Maybe you need to put the UserId on the Level Of Detail shelf, maybe it's already accounted for in the filter, it's hard to say without seeing a packaged workbook.

             

            This could be an easier solution than rawsql if it works in this case.

             

            Andrew

            • 3. Re: Lookup field using conditions in SQL?
              Dave Ulliott

              Hi Andrew,

              Many thanks for your quick response.

              Sorry, you're quite correct - would be much easier if I had uploaded an example. As you suggested, however, some of the data is sensitive so I had to scrub it.

              Attached is a much simplified version of the data I'm working on - there are multiple UIDs with a 'Load' event and an 'Install' event. These events are seperate database entries. I would like to run a calculation that will lookup the 'Install' event and return the date field. I would then like to compute the number of days between the 'Load' event and the relevant 'Install' event. This needs to then be mapped out on the x axis by date of 'Load'

               

              I started off by mapping out the Date field on the x axis and limiting the viz by restricting the event type to 'Load', however that seems to then restrict the results of the calculation you suggested.

               

              I've attached an Excel version of what I am trying to achieve (I'm much more at home with Excel!) - note I did the pivot table in Open Office on the Windows partition of my Mac - if it behaves strangely I blame that!

               

              Many thanks again - it's a fairly new job, and software package to me, so I want to try and impress!

               

              Cheers

              Dave

              • 4. Re: Lookup field using conditions in SQL?
                Dave Ulliott

                Hey Joshua,

                Sorry for the slow reply - I've been working on this today and I think I'm so close I can almost taste it!

                I'm passing a RAWSQL_DATE function back to the database using the following formula - this should return the minimum date for each UID where the event type is 'Install'

                 

                RAWSQL_DATE("SELECT %1

                                         FROM (

                                                        SELECT min(%1),%2,%3

                                                         FROM [Sheet1$]

                                                        WHERE %2 = 'Install'

                                                        GROUP BY %3, %2

                                                        ORDER BY %3, %2

                                                   )

                                          GROUP BY %1

                                          ORDER BY %1", [Date],[Event],[UID])

                 

                I...... think that should do it. But I'm getting the following error:

                Microsoft JET database error 0x80040E10: No value given for one or more required parameters.

                 

                I can't quite see where the missing parameter might be. Any ideas?!

                 

                Thanks in advance

                Dave

                • 5. Re: Lookup field using conditions in SQL?
                  Dave Ulliott

                  Hey to anybody following this, I think I cracked it.

                  I was probably over complicating the situation by following the RAWSQL route (from what I've been able to ascertain, the RAWSQL calculations are only really useful for obtaining aggregate or singular data from the underlying table, rather than doing any targeted data extrapolation.)

                  I simply edited the connection, bringing in a carbon copy of the base table, then altered the SQL on the query:

                   

                  SELECT   `gamelogs`.`entrydate` AS `entrydate`,

                                 `gamelogs`.`event` AS `event`,

                                 `gamelogs`.`uid` AS `uid`

                                 ,`gamelogs1`.`entrydate` AS `InstallDate`,

                                 `gamelogs1`.`uid` AS `Install_UID`,

                                 `gamelogs1`.`event` AS `InstallEvent`

                  FROM `default`.`gamelogs` `gamelogs`

                  LEFT OUTER JOIN `default`.`gamelogs` `gamelogs1` ON (`gamelogs`.`uid` = `gamelogs1`.`uid`)

                  WHERE `gamelogs1`.`event` = 'install'

                   

                  This results in my data extract having a new column with the minimum event date for each UID at the Install Event.

                  I then create a nice simple datedif('day',[entrydate],[InstallDate]) as my calculated field for 'DaysSinceInstall' - make this field a Dimension, stick it on the Rows shelf, Date on the Columns shelf, Number of Records on the Marks and I'm done!

                   

                  I'm not sure this is the most sensible / scalable solution, but it works for me! This has been a good introduction to Tableau - Great to see that the system has this amount of flexibility to obtain workable solutions.

                   

                  I'll sleep tonight!

                   

                  Cheers,

                  Dave

                  • 6. Re: Lookup field using conditions in SQL?
                    Andrew Watson

                    Hi Dave

                     

                    Apologies for not getting back to you earlier, busy weekend for me, but well done on figuring it out for yourself. I'm sure it's also possible to solve this using a Tableau only solution, although I'm from a database background so always deal with these situations altering the data connection using custom sql.

                     

                    If you're comfortable writing SQL once you get to grips with the nuances of tableau you'll find this a great piece of software. Tableau uses MS Jet SQL which could cause you some headaches in future if you're used to SQL Server, especially if using date functions...but I'm sure you'll overcome these issues once you run into them :-)

                     

                    Andrew