topic Re: pivot table shows rows that are scoped out in QlikView

pivot table shows rows that are scoped out

pkrouglov — Thu, 07 Apr 2016 10:36:36 GMT

Hi,

Can anybody explain what is going on with my pivot. I have an app with some 12M rows and my pivot behaves somewhat weird.

It shows rows that should not be shown at all. I've made a small example, but could not reproduce the "wrong" behavior.

So, below is the data sample

level1	level2	level3	code	dtm	type
A	B		cd1	4/7/2016 11:29	t
A	C		cd2	4/8/2016 11:29	t
A	D		cd3	4/9/2016 11:29	t
A	B		cd4	4/10/2016 11:29	t
A	C		cd5	4/11/2016 11:29	t
A	D		cd6	4/11/2016 11:29	t
A	B	r1	cd1	4/7/2016 11:29	v
A	B	r1	cd2	4/8/2016 11:29	v
A	B	r2	cd3	4/9/2016 11:29	v
A	B	r2	cd4	4/10/2016 11:29	v
A	B	r2			b
A	B	r3			b
A	C	r4			b
A	C	r5			b

Dimensions are level1..3

Expressions:

count({1<type={'v'}>} distinct code)

maxstring({1<type={'v','b'}>} dtm)

If I could reproduce the same on my sample, then

- row (type='t' and level2='D') is shown when nothing or level1 selection is made

- row (type='t' and level2='D') is not shown when selection of level3 or level2 is made

I've edited below picture to show the wrong behavior.

Re: pivot table shows rows that are scoped out

marcus_sommer — Thu, 07 Apr 2016 11:22:17 GMT

Your set analysis expression contain a 1 as set identifier which meant that all records will be considered and this could cause the displaying from uncompleted records in which some kind of data are missing - very probably this is caused by poor data-quality.

Best suited to find this is a tablebox which includes a unique field like a recno() and/or a rowno() - you need to add this within the load-script - and also to consider to use NULL handling in QlikView.

- Marcus

Re: pivot table shows rows that are scoped out

pkrouglov — Fri, 08 Apr 2016 08:02:58 GMT

Hi Markus

I've loaded the same data into a new QV file and copy-paste my pivot as object there. I've got the same weird behavior.

Then I've recreated the same pivot using UI and copy-pasted the same expressions. It worked as it should then.

I guess my pivot object was somehow corrupted.

-Pavel