Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hi Pieter,
What is your issue? Can't figure out.
Your results are fine check it:
Regards,
MB
Braga,
What are your thoughts about the chosen method to link the position numbers?
Do you have an alternative?
I think he is just trying to share his code for somebody who might find it useful
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;
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
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;