topic Re: Add ID-number in a pivot table in QlikView

Add ID-number in a pivot table

Anonymous — Wed, 04 Mar 2015 15:49:15 GMT

Hi all,

I've got a pivot table which looks as follows (simplified):

	Dimensions		Expressions

ID	ordernumber	product	Price	discount
	16	a	€ 3,00	0
		b	€ 6,23	0
		c	€ 9,46	30%
	33	d	€ 12,69	0
	33	e	€ 15,92	0
	341	f	€ 19,15	0
	395	g	€ 22,38	15%
	395	h	€ 25,61	0
	121	i	€ 28,84	0
	137	j	€ 32,07	0

Now, I would like to fill the column "ID" with a row number based on the unique ordernumber. So, ordernumber 16 is assigned a 1, ordernumber 33 is assigned a 2, ordernumber 341 is assigned a 3 etc.

I've tried to use RowNo() and RowNo(total), but this function doesn't work in the dimensions and when I use RowNo(total) in an expression it assigns the rownumber to the actual row, so the line with product a=1 and product b=2.

Your help is appreciated.

Re: Add ID-number in a pivot table

giakoum — Wed, 04 Mar 2015 15:50:30 GMT

try to combine it with Dimensionality() as an expression

Re: Add ID-number in a pivot table

JonnyPoole — Wed, 04 Mar 2015 15:52:34 GMT

Here is a sample. My 1st dimension is 'calculated' with a formula like this:

=aggr(RowNo(),Year)

Re: Add ID-number in a pivot table

Anonymous — Wed, 04 Mar 2015 16:14:31 GMT

Thanx, this works!!

Now rises a second question. Maybe you know an answer on the spot.

Suppose order 121 also contains product C. When I select product C, I would like to see the ID-number assigned by your formula. However, if I select it now, the ID-number of order 121 obviously changes to 2.

So is it maybe possible to copy this table and refer from this second table to the column ID in table 1?

Re: Add ID-number in a pivot table

JonnyPoole — Wed, 04 Mar 2015 16:46:52 GMT

I sense this is probably doable but need to give it a bit more thought. stay tuned.