Gaps in Qlik Sense Pivot table

[#1]

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?

[felipedl]

Hi Guus,

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

Felipe.

[syukyo_zhu]

Hi,

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

Hope helpful

[#4]

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.

[felipedl]

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.

[#6]

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 A	0	10	20
project B	10	5	0

And then do an unpivot operation in the database:

Project	Attribute	Value
project A	Junior	0
project A	Manager	10
project A	Support	20
project B	Junior	10
project B	Manager	5
project B	Support	0

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!

[felipedl]

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.

[#8]

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 !