10 Replies Latest reply on Oct 19, 2016 9:12 AM by Larry Schaberg

    Question on Counts From Source 1 using Left Join

    Larry Schaberg

      I have a question on a problem I am having with including information in the Tooltip. I have not been able to locate a suitable answer in the community as of yet or in the support site.

       

      I have 2 Data Sources; 1 Oracle Table and 2 Excel File. I am working on getting us away from the excel file.

       

      My 2 sources are left joined based on Employee ID. On the left side I have my Oracle Table and on the right the excel file.

       

      I am building a chart where I have departments as the row and number of submissions for the columns using a bar chart from the Excel file. This works without any issue. Counts are accurate.

       

      I am trying to include in the tool tip the total number of records, employees, that exist in the Oracle table based on the department. So far it seems as though Tableau Desktop is only counting the number of records either from the excel file or up to the amount in the excel file. There are far more employees than we have submissions in a department but the total seems to only match the submissions.

       

      I have tried adding fields from the oracle database to the marks card and use count. I have tried several other things as well with no luck.

       

      Could anyone provide insight as to if this can be done and how or a direction for me to go to solve this? It seems like it should be possible and shouldn't be that complicated. Perhaps it is because of the 2 different types of sources?

        • 1. Re: Question on Counts From Source 1 using Left Join
          Srinidhi Narayan

          1.  Is the departments dimension part of the oracle table? or Excel?

          2.  What numbers show up if you used the inbuilt "Number of Records" measure?

          • 2. Re: Question on Counts From Source 1 using Left Join
            Hima vardhan Reddy Pavuluri

            Hi,

             

            I Guess it might be the problem with Null Value, As you said you are using Fields from Excel in view that means chart will be at granular level of those excel sheet Fields. Because of Left outer join your excel sheet fields might have Null Value.

             

            Can you attach sample workbook to look into it further.

            • 3. Re: Question on Counts From Source 1 using Left Join
              Larry Schaberg

              Departments are contained in both sources and are the same value, i.e both have a department column and contain the department value of IT as an example. I did use a custom SQL query rather than dragging the table over so I could make some changes to columns.

               

              In the Excel file the column name is Department1. Oracle has 2 columns for departments, I created one with the custom SQL, and one of them has the same name as the Excel file.

               

              If I use the number of records I get the same number of submissions that exist in the Excel file.

              • 4. Re: Question on Counts From Source 1 using Left Join
                Larry Schaberg

                Unfortunately I can't. I would have to scrub and remove a tremendous amount and get approval.

                 

                There are empty cells in the Excel file and looking at the file I see there are some empty values for department. It sounds like your saying because there are some empty values in the department column in the Excel file it is causing Tableau to not return a total employee count based on the department from the Oracle Table?

                • 5. Re: Question on Counts From Source 1 using Left Join
                  Kaz Shakir

                  Can you post your custom SQL query?  It sounds like the issue may be in the SQL result - in other words, your final result that Tableau sees does not include all of the records.

                  • 6. Re: Question on Counts From Source 1 using Left Join
                    Larry Schaberg

                    At this moment we are using * while in development until we have narrowed down the exact fields we need. Then we will change the * to the actual tables. I have included the query below.

                     

                    I can see the columns and values showing in the Data Source tab and see the departments column and values showing.

                     

                    select h.*, to_char(to_number(ID)) Person, DEPT_ID Department1

                    from sometable H

                    • 7. Re: Question on Counts From Source 1 using Left Join
                      Kaz Shakir

                      Well, here's another suggestion for you.  Try creating a new calculated field that uses an LOD (level of detail) expression.  It might look something like this:

                       

                      NumberOfEmpPerDepartment

                      {FIXED [Department(Oracle_Source)]: COUNT([EmployeeID(Oracle_Source)])}

                       

                      And then place this new calculated field on the "Detail" shelf - and it should appear in your tooltip for the chart.

                       

                      And for your chart, I would show the [Department(Excel)] field on on the row shelf, and the [CNT(Employee(Excel))] on the columns shelf.  Something like this:

                       

                       

                      Would that give you what you are trying to achieve?

                      2 of 2 people found this helpful
                      • 8. Re: Question on Counts From Source 1 using Left Join
                        Larry Schaberg

                        This brings me very close. I am going to mark it correct because it does do exactly what is needed, answers the question I had posted, and I think normally would be perfect.

                         

                        When delving into the numbers for verification I found one issue with using this that I didn't catch before because I wasn't close to getting the answer. I found the database we have includes all employees, active and inactive. The count I need is only for active employees for each department because we also want to include the % of the total also. i.e. 15 people in a department of 40 submitted which is % of the total employees for that department.

                         

                        I tried the following modification which in some cases brings me close to the number I need to display but in others it is no where near close. I am not sure what may be incorrect about this or if this is the right thing to use in order to achieve what we need.

                         

                        {FIXED [DEPT_ID]: COUNT(iif([PERSON_STATUS_CODE]='A',[PERSON],null))}

                         

                        The only other thing I can think of is to create another custom query where we only include perhaps department and employee id where the employee is active then use the FIXED statement to do the calculation. I wasn't sure if this would be the best route for performance and other reasons.

                        • 9. Re: Question on Counts From Source 1 using Left Join
                          Kaz Shakir

                          Glad that solution helped get you closer to the answer.

                           

                          In your modification, would it give you the right answer if you change it slightly as follows:

                           

                          {FIXED [DEPT_ID]: SUM(iif([PERSON_STATUS_CODE]='A',1,0))}

                           

                          ?

                          • 10. Re: Question on Counts From Source 1 using Left Join
                            Larry Schaberg

                            I tried that one except I didn't change count to sum. Either way it doesn't give me the exact number.

                             

                            I will delve into the data more to see if I can figure out why.