Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Gaps in Qlik Sense Pivot table

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?

7 Replies
Partner
Partner

Re: Gaps in Qlik Sense Pivot table

Hi Guus,

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

Felipe.

Highlighted
syukyo_zhu
Contributor III

Re: Gaps in Qlik Sense Pivot table

Hi,

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

Hope helpful

Not applicable

Re: Gaps in Qlik Sense Pivot table

Hi Felip,

The pivoting is done in the Pivot table.

Data is imported from:

Project, Seniority, Amount

project A, Manager, 10

project A, Support, 20

project B, Manager, 5

project B, Junior, 10

So, 4 records to fill 6 amounts, the missing 2 are the gaps.

Partner
Partner

Re: Gaps in Qlik Sense Pivot table

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.

Not applicable

Re: Gaps in Qlik Sense Pivot table

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!


Partner
Partner

Re: Gaps in Qlik Sense Pivot table

Attached the qvf with one solution.

You must keep in mind that this will add some rows and if you have a lot of combinations that will expand your base quite quickly.

Not applicable

Re: Gaps in Qlik Sense Pivot table

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 !