Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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];
Sunny, cant we use this in front end like,
Dim: All the fields except SEQUENCE_NUM
Expr: = Aggr(Max(SEQUENCE_NUM), [PO Number])
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)
Yes Firstsortedvalue will also work, but thought of just writing one expression. 🙂
See this is how it helps me from experts like you, to think different alternative ways to do. Appreciate that buddy.
Same goes for you brother. You help me think differently as well, so thanks to you as well