Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
Not applicable

field max in the table

hello,
I have a table of QlikView this:

I need to turn this table by display only the row with the maximum value of the field cost with the aggregation on the field A.

in this way:   

ABdatamax costo
000010003012/12/20141,737
000020000112/12/20141,735
000020002412/12/20141,735
000190002412/12/20141,735
000245535712/12/20141,735
000275144312/12/20141,615
000300002412/12/20141,735
000330004112/12/20141,745
000350003612/12/20141,754
000360004012/12/20141,754

What kind of transformations / expressions should I enter?

Thanks
Alex

Tags (3)
1 Solution

Accepted Solutions
MVP
MVP

Re: field max in the table

Create a Straight Table

Use

Dimension

A

and below three different expressions

FOR B

=FirstSortedValue(Distinct B, -Aggr(SUM(costo),A,B))

FOR data

=FirstSortedValue(Distinct data, -Aggr(SUM(costo),A,B))

For SUM(costo)

=FirstSortedValue(Distinct Aggr(SUM(costo),A,B), -Aggr(SUM(costo),A,B))

6 Replies
mato32188
Contributor III

Re: field max in the table

Hi Alessandro,

switch it to straight table and create expression max(value).

M

Partner
Partner

Re: field max in the table

Hello

You can try with firstsortedvalue function.

MVP
MVP

Re: field max in the table

Create a table

Dimension

A

Expression

=FirstSortedValue(Distinct B, -Aggr(SUM(costo),A,B))

=FirstSortedValue(Distinct data, -Aggr(SUM(costo),A,B))

=FirstSortedValue(Distinct Aggr(SUM(costo),A,B), -Aggr(SUM(costo),A,B))

Not applicable

Re: field max in the table

that way I lose the information of the field b.
Could you write me an example.
Thank you very much
Alex​

Not applicable

Re: field max in the table

I did not understand where I should insert the given expression.
you give me an example explicit about it?
thanks
Alex

MVP
MVP

Re: field max in the table

Create a Straight Table

Use

Dimension

A

and below three different expressions

FOR B

=FirstSortedValue(Distinct B, -Aggr(SUM(costo),A,B))

FOR data

=FirstSortedValue(Distinct data, -Aggr(SUM(costo),A,B))

For SUM(costo)

=FirstSortedValue(Distinct Aggr(SUM(costo),A,B), -Aggr(SUM(costo),A,B))