Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

Group rows keeping a reference to the row position

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

6 Replies
Highlighted
Specialist III
Specialist III

Re: Group rows keeping a reference to the row position

Hi Pieter,

What is your issue? Can't figure out.

Your results are fine check it:

Sem Título3.png

Regards,

MB

Highlighted
Contributor III
Contributor III

Re: Group rows keeping a reference to the row position

Braga,

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

Do you have an alternative?

Highlighted

Re: Group rows keeping a reference to the row position

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

Highlighted

Re: Group rows keeping a reference to the row position

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;

Highlighted
Specialist III
Specialist III

Re: Group rows keeping a reference to the row position

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

Highlighted
MVP & Luminary
MVP & Luminary

Re: Group rows keeping a reference to the row position

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;


talk is cheap, supply exceeds demand