topic Group rows keeping a reference to the row position in QlikView

Group rows keeping a reference to the row position

Anonymous — Wed, 11 Nov 2015 10:43:51 GMT

Here is some code i want to share.

It is a group by statement keeping track of the reference to the detail row.

It has proven to be usefull a few times.

load * inline [position_row,Groupvalue,sales
1,     A,     1500
2,     A,     1200
3,     A,     956
4,     A,     120
5,     B,     2300
6,     B,     234
7,     B,     1278
8,     B,     3456
9,     C,     1234
10,     C,     599
11,     C,     600
12,     C,     120
13,     C,     4600
14,     D,     3511
15,     D,     3400
16,     D,     1230
17,     D,     1200];

Maxsales:
load
Groupvalue,
SubField(maxtext,'|',3) as [grp sales],
SubField(maxtext,'|',2) as [grp position_row];
LOAD Groupvalue,
     Maxstring(text(100000000+sales) &'|' & text(position_row)&'|' & text(sales)) as maxtext
resident testdata
group by Groupvalue;

//------Result-----
//Groupvalue grp position_row grp sales
//A      1      1500
//B      8      3456
//C      13      4600
//D      14      3511

Re: Group rows keeping a reference to the row position

miguelbraga — Wed, 11 Nov 2015 12:20:16 GMT

Hi Pieter,

What is your issue? Can't figure out.

Your results are fine check it:

Regards,

Re: Group rows keeping a reference to the row position

Anonymous — Wed, 11 Nov 2015 12:31:53 GMT

Braga,

What are your thoughts about the chosen method to link the position numbers?

Do you have an alternative?

Re: Group rows keeping a reference to the row position

sunny_talwar — Wed, 11 Nov 2015 12:49:55 GMT

I think he is just trying to share his code for somebody who might find it useful

Re: Group rows keeping a reference to the row position

sunny_talwar — Wed, 11 Nov 2015 13:01:35 GMT

An alternative could be this (Making changes to the sample that Braga's created)

testdata:

load * inline [position_row,Groupvalue,sales

1, A, 1500

2, A, 1200

3, A, 956

4, A, 120

5, B, 2300

6, B, 234

7, B, 1278

8, B, 3456

9, C, 1234

10, C, 599

11, C, 600

12, C, 120

13, C, 4600

14, D, 3511

15, D, 3400

16, D, 1230

17, D, 1200];

Right Join (testdata)

LOAD Groupvalue,

Max(sales) as sales

Resident testdata

Group By Groupvalue;

Re: Group rows keeping a reference to the row position

miguelbraga — Wed, 11 Nov 2015 13:01:41 GMT

Hey again,

I think your method is quite good actually, finding the greatest value in each group (A, B, C or D) in a simple and understandable way is a great feat. Good job, I'll recomend this method and use it in future approaches if needed

Regards,

Re: Group rows keeping a reference to the row position

Gysbert_Wassenaar — Wed, 11 Nov 2015 14:58:22 GMT

I usually use the firstsortedvalue function

load * inline [position_row,Groupvalue,sales
1,    A,    1500
2,    A,    1200
3,    A,    956
4,    A,    120
5,    B,    2300
6,    B,    234
7,    B,    1278
8,    B,    3456
9,    C,    1234
10,    C,    599
11,    C,    600
12,    C,    120
13,    C,    4600
14,    D,    3511
15,    D,    3400
16,    D,    1230
17,    D,    1200];

Maxsales:
LOAD Groupvalue,

FirstSortedValue(position_row,-sales) as grp_position_row,
Max(sales) as grp_sales
resident testdata
group by Groupvalue;