5 Replies Latest reply: Dec 3, 2016 9:07 AM by Vegar Lie Arntsen RSS

    Display a dimension based on another field

      Dear Qlik gurus,


      I'm a Qlik newbie, currently working on a payroll data which contains fields such as Full Name, Employee ID, SSN.

      For one histogram chart, I want to show each employee's regular hours vs his/her overtime, but "one employee" looks suspicious and then I realized there're two employees with exact same name.


      Is it possible to display the dimension "full name" based on their SSN number (which is more like the true dimension)? Or in other words, let Qlik add the numbers only when the SSN is same.


      Thanks a lot for your insights!!!

        • Re: Display a dimension based on another field
          Robert Mika

          Could you explain little bit more what are you trying to achieve?

            • Re: Display a dimension based on another field

              Sorry for the confusion.


              A simple example will be as follows:


              Employee ID     Name              SSN             Salary

              001111           Emily Kale       545429234     $54,000

              001112          John Martin      212755484    $82,000

              001113          Samuel Lee      854212521    $74,000

              001114          John Martin      851214444    $39,000


              I want to create a histogram to show each employee's salary, so I choose "Name" as dimension, and "Salary" as mesure, then the chart I get is 3 names, John Martin's salary become $121,000 ($82,000+$39,000). Is there a way that I can still see four employees with their respective salary ?  I prefer not to change the dimension "Name" because I need it to be displayed.


              Thanks so much for your help.

                • Re: Display a dimension based on another field
                  Robert Mika

                  How then will you know which John is which?

                  or maybe you will, but somebody looking at the graph will be confused.


                  You can add a prefix or suffix like:

                  EmployeeID &Name as Name

                  RecNo()&Name as Name

                  in the load script but that will assign EmployeeID or  record number to each name.


                  Do they have a second name?

                  Maybe adding additional information at the source of your data will help?

                  Otherwise, I do not see a way to convict QV that John Martin<>John Martin

                  • Re: Display a dimension based on another field
                    Ganesh S

                    As explained by Robert, it will be confusing for any user or even we as a developer to differentiate between two occurrence of John Martin in chart even if it is shown in two separate rows.


                    So, it is mandatory to provide additional info about the Name field that will differentiate John Martin.


                    In your case, it will be Emp ID which can differentiate the two John Martin.


                    Rather going on for finding solution for this, instead you should make understand your Functional or User abt this consequence.

                • Re: Display a dimension based on another field
                  Vegar Lie Arntsen

                  A  suggestion to avoid merging your employees together is define them as a dual().


                  DUAL(Name, SSN) as Person