2 Replies Latest reply on Feb 18, 2012 9:04 AM by Michel Rathe

    Multi joins problems

    Michel Rathe

      Hi everyone,

       

      Having tremendous difficulties figuring that one out!

      I've been using Tableau since version 1.5 but never had to go for multiple joins on sql tables (Though my example is from excel) My background is limited as far as tables architecture is concerned.

       

      I have 3 sql tables I'd like to join and everyone of them will render a calculation that will be part of a ratio.

       

      1- Table 1:Availability: date,emp_no, hour_start, hour_end ,and some other fields

      2- Table 2:NONAvailability: date,emp_no, hour_start, hour_end ,and some other fields

      3- Table 3:Worked:date,emp_no, hour_start, and some other fields

       

      The basic idea is to have a formula in Tableau that check that if the hour_start >= 6 and hour_end <=15 then it is a day shift and the output is 1 (for summing)

      Same thing in table 2 and 3. So there will be 3 formulas for the day shift (Availability, Non Availability, worked)

      After there is a formula between table 1 and 2 that produce the net between Availability and Non Availability, worked (Avail.day shift MINUS NonAvail.dayshift).

       

      Same for night and midnight shift. At the end, for one day for 1 employee, if the net of all the shift (Day, night and midnight) is at least 1 the there is a count of one for that employee. (even if there is 3 avail. and no Non avail, thus a net of 3 the count of avail. final is 1 (basically if one give 3 shift avail. he will work only 1 shift)

       

      Same has to be done for the worked file (just on the start_hour). So for 1 week if a employee worked 3 days on a 5 days net availability the ratio is 3/5, or 60%. Pretty simple in excel.

       

      But because sometimes employees are called to work but do not have availabilities the ratio comes false because from a join point of view records on the worked finds inexistant date in the Avail. and Non Avail tables.

       

      So I included a perpetual sheet (calendar$)  in excel with a date fields ranging from jan.1 2011 to 2032...

       

      Basically, for verification purposes, I would like to see on 1 line :

       

      Example based on a day shift only...

       

      (Date)          (Emp_no) (Formula Avail) (Formula NonAvail) (Formula net avail.)   (Formula if at leat 1 net avail.) (Formula worked) Ratio

      1-10-2011     148               3                         1                         2                                        1                                        1

      and so on...

       

      The ratio HAS to maintain integrity trough dimensions (day, month, years, employee and other.

       

      So basically I'm dealing with 2 fact tables... I've tried a lot of different things and different variations

           1- Blending

           2- sql statement with join with UNION for the fisrt 2 tables since they are alike (result of all the calculation worked fine at that point) but joining      the worked one after and the calendar do not work!

                Remember that the 2 first tables have to be select distinct because of the way the data are stored.

                the worked table don't have to be distinct.

          

      So my question is how to set up from the SQL custom statement the tables so that:

           1- all my calculation will be fine troughout all the dimensions

           2- Allow me to permute dimension ex: emp/date or date/emp or any other ways.

       

      I don't have the knowledge to build a cutom sql statement that will do the job and I'm shure that my needs are finally maube simple.

       

      I'd appreciate a quick answer on that since I'm way past deadline.

       

      Thanks a lot,

       

      Michel 

        • 1. Re: Multi joins problems
          Alex Kerin

          Honestly, I don't think you'll get an answer here - it seems that you're trying to get Tableau to behave like an application for scheduling purposes. It's difficult to follow exactly what you want.

          • 2. Re: Multi joins problems
            Michel Rathe

            Thanks Alex,

             

            The views I'm trying to build is standard summing aggregation once the formulas and the perfect sql statement are performed. My ratio is no more than a margin% on sales by comparaison.

             

            My main interrogation is the setup of the tables by custom sql statement.

             

            I know there is a simple way to set that up but my knowledge in sql statement is limited.

             

            Thanks,

             

            Michel