topic Re: Max of a field in QlikView

Max of a field

Mon, 26 Sep 2016 06:54:49 GMT

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

Re: Max of a field

sunny_talwar — Mon, 26 Sep 2016 16:30:16 GMT

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];

Re: Max of a field

vishsaggi — Mon, 26 Sep 2016 16:41:19 GMT

Sunny, cant we use this in front end like,

Dim: All the fields except SEQUENCE_NUM

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

Re: Max of a field

sunny_talwar — Mon, 26 Sep 2016 16:44:24 GMT

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)

Re: Max of a field

vishsaggi — Mon, 26 Sep 2016 16:47:04 GMT

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

Re: Max of a field

vishsaggi — Mon, 26 Sep 2016 16:48:52 GMT

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

Re: Max of a field

sunny_talwar — Mon, 26 Sep 2016 17:40:02 GMT

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