Problem with rowno() in pivot table

[#1]

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!

[marcus_sommer]

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

[avinashelite]

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

[#4]

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!

[marcus_sommer]

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

[#6]

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!

[settu_periasamy]

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
];

[marcus_sommer]

Try something like this:

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

     or

aggr(rank(YourExpression), Group, Volumen)

- Marcus

[#9]

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!

[#10]

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!