topic Re: Pivot table, how to create empty collumn in QlikView

Pivot table, how to create empty collumn

Tue, 23 Apr 2013 10:40:13 GMT

Hi,

how can i insert 1 or more empty "divider" collumn in a pivot table ?

1. excel file, which determines which data should be shown in the pivot table (dimensions)

Nr.	ID	location
1	11	here
2	space	here
3	13	here
4	24	there
5	25	there

2. Pivot table, which displays

NR	1		3		4		5
Name	Alice		Charly		Diego		Emil
Location	A	B	A	B	A	B	A	B
here	100	200	100	200	100	200	0	0

but ist should display space too.

NR	1		2		3		4		5
Name	Alice		SPACE		Charly		Diego		Emil
Location	A	B	A	B	A	B	A	B	A	B
here	100	200			100	200	100	200	0	0

of course, there are no values or dates for the name "space" because its a placeholder.

hide empty cells is disabled.

Any suggestions ?

Re: Pivot table, how to create empty collumn

kuba_michalik — Tue, 23 Apr 2013 11:19:33 GMT

That's probably because SPACE is represented as null value, and null values in dimensions don't show if they have no expression values associated.

You should read it in as something other than a null using NullValue, or, for a quick and dirty solution, just stick a hard space there in Excel 😉 Of course, if it's not null, it would show everywhere you make a selection on ID (listboxes etc.) but maybe it's not a problem in your case. Also, if it has no dates etc. associated, it would disappear every time you make a selection on date, and so on.

Re: Pivot table, how to create empty collumn

Tue, 23 Apr 2013 11:33:17 GMT

Thanks for the reply Kuba_Michalik,

i thought about giving space the ID 0000, but unfortunately, this was already taken.

of course, since the "space" ID has no data, its not listed where this data is required for display.

i thought that there is some workaround for that - some function that provides a placeholder.

selections in listboxes are a problem too - the date for the displayed data has to be choosen by the user.

i seems that i have to create dummy data for the placeholder collumn. at least i can insert it as often as i want then.

so dummy data til 2050 it is then. i thought that there must be something like a dummydata(0,'#,##') function.

Re: Pivot table, how to create empty collumn

Tue, 23 Apr 2013 15:09:33 GMT

yes, hide empty cells / suppress zero-values is disabled.

At the Moment its necessary to use dummy values for the "placeholder collumn". But it can be used as many times as necessary.

Re: Pivot table, how to create empty collumn

Wed, 24 Apr 2013 08:07:18 GMT

Yes, hide empty cells / suppress zero-values is disabled.

At the moment its necessary to use dummy values for the placeholder unit.

if one unit has no data in the selected timeframe (february = ok, march = missing), instead of an empty collumn with the unit name, its not shown at all.

so the user sometimes doent even know that the data is missing

Re: Pivot table, how to create empty collumn

calvindk — Wed, 24 Apr 2013 09:14:10 GMT

I can't seem to replicate your issue. Can you upload your specific one?
See attached.