First off, I wish I could just attach my file Tableau file.. but it still wants to connect to an offsite DB and when the end user opens it, even with the data extracted, it still prompts you to connect to the DB.. so I guess that's out of the question. I have an Excel version, but that's easy... the point in using Tableau is to connect to the real-time data via the DB, and that's the root of my issue. I am going to be as detailed as possible.
I have created to tables... both from the same database (the same 160 column table) using custom SQL queries so I could filter down the data that would give me my defect counts and my machine counts.
Think of this main DB table as a table of defects (events)... 160 columns and countless rows explaining the specifics of the defect. Most are actually defects, some are not and are just created so we can count the machine.
Filtered down via Custom SQL:
-Table 1 is called Machines (# of machines built) - by itself, this gives me an accurate representation of the total # of machines I've built. - starting on date 10/1/2015 it should be +/- 5,460
Vital Columns include: Event #, Serial #, Date, Product Family
-Table 2 is called Defect (# of defects while building the machine) - by itself, this gives me an accurate representation of the total # of defects - starting on date 10/1/2015 it should be just over 30,600
Vital Columns: Event #, Serial #, Date, Product Family, and a bunch of other unrelated columns I don't care about right now.
Each machine is linked to an Event #, each machine has a serial number, each machine belongs to a product family, and each machine was completed on a particular date.
Each defect is given an Event #, each defect is tied to a serial #, each defect (because it is tied to a serial #) is tied to a product family, and each defect is given a date based on the date the defect was entered into the system.
Goal: Get the Defects per Unit (Defects/Machines) - DPU
Easy. So easy in Excel. This is ridiculous. But since my ignorance is diminishing my want to learn and use Tableau... I am at a crossroads.
My unique identifiers (columns that are the same in both tables):
I would guess common columns, but this is tricky in my mind. Because nothing is technically unique. I'm pulling the same data twice, just using different filters for each table.
For example: An Event from my defects table, with all of the same information can be found on the machine table with the same exact information.
So... when I... for example do a left join (or right) I get an accurate representation of one, but not the other. Nonetheless, I'm just counting. But what about breaking down the DPU by Year, Month, Week, Day? Ugh! What am I missing... I've taken courses, I've watched videos... There are dots just not connecting.
I've added an extract snapshot of the DB, as well as the text files for my basic custom SQL queries. In additional, I've also included a document detailing the specifics of the filters. If you actually get this far, you don't need to tell me our DB setup is convoluted, but it's what I have to work with. If anyone has the time to actually look into this... awesome. If not, then I'll continue my work in the den with the wolves. Thank you all.