Max of a field

[#1]

Dear All,

I have a requirement where i have to show only one record(row) based on the last PO Approver Field and where SEQUENCE_NUM is maximum per Requisition. In the below case  the sequence no should be 6 and PO approver should be Mr. Raju P S . How can i acheive this in front end.

Org Name	PO Number	Line Num	PO Creation Date	PO Approved Date	Req No	Buyer Name	PO Approver	SEQUENCE_NUM	Supplier Name	Item Code
F05 Fal-Sahibabad-Eng-Meat-Org	1.816E+09	1	05-06-2016	18-06-2016	1.816E+09	Mr. Raju P S	Mr. Mohammed Abdul Haq	4	NP CHEM TREAT ENGINEERING CO.	L02100401009
F05 Fal-Sahibabad-Eng-Meat-Org	1.816E+09	1	05-06-2016	18-06-2016	1.816E+09	Mr. Raju P S	Mr. Raju P S	6	NP CHEM TREAT ENGINEERING CO.	L02100401009

Any help would be much appreciated.

Many thanks,

Anam

[sunny_talwar]

I guess this need to be done for each PO Number, try this:

Table:

LOAD [Org Name],

    [PO Number],

    [Line Num],

    [PO Creation Date],

    [PO Approved Date],

    [Req No],

    [Buyer Name],

    [PO Approver],

    SEQUENCE_NUM,

    [Supplier Name],

    [Item Code]

FROM

[https://community.qlik.com/thread/234105]

(html, codepage is 1252, embedded labels, table is @1);

Right Join (Table)

LOAD [PO Number],

  Max(SEQUENCE_NUM) as SEQUENCE_NUM

Resident Table

Group By [PO Number];

[vishsaggi]

Sunny, cant we use this in front end like,

Dim: All the fields except SEQUENCE_NUM

Expr: = Aggr(Max(SEQUENCE_NUM), [PO Number])

[sunny_talwar]

Sorry, I did not see that this is needed on the front end. You can use as you have mentioned or you can use FirstSortedValue

Dimension

PO Number

Expressions

1) FirstSortedValue(FieldName1, -SEQUENCE_NUM)

2) FirstSortedValue(FieldName2, -SEQUENCE_NUM)

.

.

.

and finally use Max(SEQUENCE_NUM)

[vishsaggi]

Yes Firstsortedvalue will also work, but thought of just writing one expression. 🙂

[vishsaggi]

See this is how it helps me from experts like you, to think different alternative ways to do. Appreciate that buddy. 

[sunny_talwar]

Same goes for you brother. You help me think differently as well, so thanks to you as well