Group rows keeping a reference to the row position

[Anonymous]

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

[miguelbraga]

Hi Pieter,

What is your issue? Can't figure out.

Your results are fine check it:

Regards,

MB

[Anonymous]

Braga,

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

Do you have an alternative?

[sunny_talwar]

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

[sunny_talwar]

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;

[miguelbraga]

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,

MB

[Gysbert_Wassenaar]

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;