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
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,