topic Problem with rowno() in pivot table in QlikView

Problem with rowno() in pivot table

Thu, 11 Feb 2016 12:01:53 GMT

Hi!

I have this dataset:

And I have the following table, in which I exclude "ID_1 = 125":

The Priority column is made with rowno() function, so I don't know why it shows the value '2' for "ID_1=126" row, when it would have to be '1', because there is only one 'ID_1' for group 'B'. I thought that rowno() shows the position in the current table, and recno() shows the position in the dataset, it doesn't happen here. The dimensions are:

- Group: =if(ID_1<>125,Group)

- Volumen: =Aggr(count(if(ID_1<>125,ID_1)),Group)

- Priority: =aggr(if(ID_1<>125,rowno()),Group,ID_1)

- ID_1: =if(ID_1<>125,ID_1)

- ID_2: =if(ID_1<>125,ID_2)

Thanks in advance!

Re: Problem with rowno() in pivot table

marcus_sommer — Thu, 11 Feb 2016 13:09:03 GMT

Rowno() within the gui isn't the same as rowno() within the script - if you want to use the position of values within the tables then create rowno() as RowNo and/or recno() as RecNo as separate fields and use them within the gui.

- Marcus

Re: Problem with rowno() in pivot table

avinashelite — Thu, 11 Feb 2016 13:21:15 GMT

are you using the rowno() in the script ? if so then its behaving currectly .....rowno() will change in case of the conditional load ...try to use the rowno() in front and check

Re: Problem with rowno() in pivot table

Fri, 12 Feb 2016 11:48:38 GMT

Hi!

First of all, thank you for your answers! I've followed these steps:

1. My current script is:

2. I've added a new dimension in the table (third column):

=aggr(if(ID_1<>125,[RowNo( )]),Group,ID_1)

But it is not correct, because in this column I would like to show the order of each id group by its group. Therefore, the values for the third column have to be: 1;1;1,2,3,4;1.

May be I didn't understand your answers...

Thanks for your help!

Re: Problem with rowno() in pivot table

marcus_sommer — Fri, 12 Feb 2016 19:49:11 GMT

Sorry, I don't understand what do you want to achieve. An alternatively to your calculated dimension could be to create these information as normal expression or to build this within the script with Peek() or Previous() ? with which you could read the values from the previous records and react appropriate to accumulate data or to create any kind of counting the records.

- Marcus

Re: Problem with rowno() in pivot table

Tue, 16 Feb 2016 12:08:58 GMT

Sorry, may be I've explained myself really bad... In the Priority column, I only want to set the ID_1 priority in each group in the registers showed in the table, that is, the order of each register within its group. Therefore, the correct result has to be the following table:

Group	Volumen	Priority	ID_1	ID_2
A	1	1	124	A_124
B	1	1	126	A_126
C	4	1	127	A_127
		2	128	A_128
		3	129	A_129
		4	130	A_130
D	1	1	131	A_131

I think that it has to be easy, but I'm not able to achieve it with expressions because I'm new in Qlikview.

Thanks for your effort Marcus!

Re: Problem with rowno() in pivot table

settu_periasamy — Wed, 17 Feb 2016 07:53:35 GMT

May be like this..

LOAD *,AutoNumber(RecNo(),Group) as Priority;
LOAD * INLINE [
Group, ID_1, ID_2
A, 124, A_124
B, 126, A_126
C, 127, A_127
C, 128, A_128
C, 129, A_129
C, 130, A_130
D, 131, A_131
];

Re: Problem with rowno() in pivot table

marcus_sommer — Wed, 17 Feb 2016 07:55:36 GMT

Try something like this:

aggr(rank(max(ID_1)), Group, Volumen)

aggr(rank(YourExpression), Group, Volumen)

- Marcus

Re: Problem with rowno() in pivot table

Mon, 22 Feb 2016 10:13:06 GMT

Hi settu_periasamy‌! First of all thanks for your answer! The problem in your approach is that you remove the "ID_1 = 125" register in the load inline, but I would like to load the whole dataset, and then in the table, remove the register "ID_1 = 125" with a filter. Do you know how I can get it? I've tried a lot of possibilities so far, and I'm not able to get the correct solution...

Thanks in advance!

Re: Problem with rowno() in pivot table

Mon, 22 Feb 2016 13:12:02 GMT

Hi Marcus_Sommer‌ ! First, I don't know how can I use "aggr(rank(max(ID_1)), Group, Volumen)" or "aggr(rank(YourExpression), Group, Volumen)", because Volumen is a calculated dimensión. Therefore, I've tried with the following dimensions:

- Group: =if(ID_1<>125,Group)

- Volumen: =Aggr(count(if(ID_1<>125,ID_1)),Group)

- Priority: =aggr(if(ID_1<>125,rank(ID_1)),Group,ID_1)

- ID_1: =if(ID_1<>125,ID_1)

- ID_2: =if(ID_1<>125,ID_2)

And it seemed that it showed the correct result:

But the order has changed and I don't know the reason. In addition, if I change 'ID_1=125' for 'ID_1=128' in the filter, the Priority in Group C is wrong, because it showed Priority = 4 instead of Priority = 3 (there are only three items).

I don't understand why is so difficult to get this functionality, because it seems easy but for me it's really difficult...

Thanks for your effort mates!

Re: Problem with rowno() in pivot table

settu_periasamy — Mon, 22 Feb 2016 13:46:13 GMT

May be like this..

Re: Problem with rowno() in pivot table

swuehl — Mon, 22 Feb 2016 13:54:27 GMT

Not really sure what you are trying to achieve or why you are using a calculated dimension for priority, but with a small modification of your original dimension:

=aggr(If(Only({<ID_1 -= {125}>}ID_1), rowno()),Group,ID_1)

Group	Volumen	Priority	ID_1	ID_2
A	1	1	124	A_124
B	1	1	126	A_126
C	4	1	127	A_127
		2	128	A_128
		3	129	A_129
		4	130	A_130
D	1	1	131	A_131

Re: Problem with rowno() in pivot table

swuehl — Mon, 22 Feb 2016 14:15:31 GMT

Attached another approach using an expression.

Re: Problem with rowno() in pivot table

Tue, 23 Feb 2016 10:47:43 GMT

Yees swuehl‌!! Your approach is correct First, I've tried without ID_1 = 125 and then without ID_1 = 128, and it works nice in both cases!!

Without ID_1 = 125:

Without ID_1 = 128:

Thanks all for your great effort!

Best regards,

Alejandro.