topic Re: Re: Need some technique to pad missing data in pivot table in QlikView

Need some technique to pad missing data in pivot table

Wed, 30 Oct 2013 14:37:46 GMT

The table is intended for export to excel and needs to be in a specified format - the field names are arbitrary in my example. The goal is to have the table display as below.

CUSTOMER QUESTION PREFERENCE CURRENT MONTHYEAR ANSWER

Customer X Question 1 First Choice 6

Second Choice 3

Third Choice 0

Question 2 First Choice 3

Second Choice 0

Third Choice 2

Customer Y Question 1 First Choice 1

Etc.

However if there is no data for either Question N or First, Second Or Third Choice in the database I need the pivot table to retain the above structure but display a 0 for the final column value. I understand I will need to pad the data but don't know the way to do this.

Without padding the missing data just gets dropped from the pivot table meaning the structure is incorrect when exported.

Re: Need some technique to pad missing data in pivot table

Wed, 30 Oct 2013 14:49:07 GMT

Go to Chart Properties > Presentation > uncheck the box next to 'Suppress Zero-Values'

Re: Need some technique to pad missing data in pivot table

Wed, 30 Oct 2013 14:55:20 GMT

I tried this but it doesn't work - the issue is that the data is missing, not that it is zero value. As soon as you reload the data the pivot table drops all the rows where there is no data. I also tried the missing symbol but this is against the whole pivot table not against a specific field, so again this doesn't work for me.

Re: Need some technique to pad missing data in pivot table

Wed, 30 Oct 2013 14:59:35 GMT

Did you try checking the box 'Show All Values' on Dimensions. If that doesn't work, I would need to see the data structure.

Re: Re: Need some technique to pad missing data in pivot table

Wed, 30 Oct 2013 15:14:22 GMT

Sorry I don't know how to attachfiles - here's the excel data

This Months DATA:

CUSTOMER	QUESTION	PREFERENCE	ANSWER
FRED	A	SALT	2
FRED	A	PEPPER	3
FRED	A	WATER	4
FRED	B	SALT	6
FRED	B	PEPPER	3
FRED	B	WATER	1
JOE	A	PEPPER	2
JOE	A	SALT	3
JOE	A	WATER	1
JOE	B	SALT	5
JOE	B	PEPPER	3
JOE	B	WATER	2
LAZY	A	WATER	4
LAZY	A	SALT	2
LAZY	B	PEPPER	1

CUSTOMER

FRED

JOE

LAZY

PREFERENCE

SALT

PEPPER

WATER

QUESTION