7 Replies Latest reply: Jul 7, 2017 3:17 AM by Guus van Zuylen RSS

    Gaps in Qlik Sense Pivot table

    Guus van Zuylen

      Hi there,


      I have a very basic pivot table, Hours worked per project and per level of seniority.

      So, e.g.

      Project                     Manager  Junior   Support

      project A                        10           -           20

      project B                        5         10             -

       

      The dashes are gaps, there are no records with hours for a junior for project A.

      I do a conditional formatting on the values: < 10 Red and >= 10 Green.

      The gaps are not influenced by my formatting script.

       

      Is it possible to join the gaps in the conditional formatting and to display 0 instead of a dash?

       

        • Re: Gaps in Qlik Sense Pivot table
          Felip Drechsler

          Hi Guus,

           

          I've made a simple example with the data you provided, see the attached qvf.

           

          Felipe.

          • Re: Gaps in Qlik Sense Pivot table
            xia ZHU

            Hi,

             

            Could you try to charts propretise\presentation and change default value for nul symbol and missiing symbol from '-' to '0'.

             

            Hope helpful

              • Re: Gaps in Qlik Sense Pivot table
                Felip Drechsler

                When you do the load statement, you can add something like this

                 

                Table:

                Load

                     *,

                     if(len([Field name]>0,[Field name],0) as [Field name]

                From [xxxx];


                This would fill the empty values with 0.


                Felipe.

                  • Re: Gaps in Qlik Sense Pivot table
                    Guus van Zuylen

                    The thing is:

                    I have 6 data points to fill with 4 records, so 2 gaps.

                    When I select only project A, I don't have a gap, there is no value for Junior, it is not displayed.

                     

                    Project                     Manager  Support

                    project A                        10         20

                     

                    The only thing I can think of is doing a pivot operation on my database view:

                    Project      Junior      Manager      Support

                    project A                        10               20

                    project B       10               5

                     


                    Fill the gaps with zeroes

                        

                    Project Junior Manager Support
                    project A01020
                    project B1050


                    And then do an unpivot operation in the database:

                       

                    ProjectAttributeValue
                    project A Junior0
                    project A Manager10
                    project A Support20
                    project B Junior10
                    project B Manager5
                    project B Support0


                    Now two new rows have been created.

                    Unfortunately, my table of 6 million rows has now doubled to 12 million and I hope Qlik sense can handle this!


                  • Re: Gaps in Qlik Sense Pivot table
                    Guus van Zuylen

                    I got it working with the method I discussed with Felip Drechsler.

                    But adding records with value 0 is a very artificial solution.

                     

                    I cannot find the option which you suggest, to change the way NULL is displayed.

                     

                    Is there a way to do this in Qlik Sense?

                    All the answers I find when I google this problem refer to Qlik View, not Sense !