Help in removing the records

[sharankv]

Hi Experts,

Please help me in filtering the records from the below table. The table has a field named as "Assignment End Date". In the below table, there are five records coming up by adding all the dimensions in the chart with "1" as expression value. I need the table to be turned to just 2 records which are the ones with the maximum Assignment End Date

Please provide me with accurate and quick solution.

Employee Number	Employee Name	Function	Profit Centre	Position	Termination Date	TerminationsEmployeeData.EmpCategory	Assignment End Date	Last Update Date	=1
19528	Vig, Minty	Audit	Audit-Business	Analyst	26-May-2014	Confirmed Staff	30-Sep-2011	08-Jul-2014	1
19528	Vig, Minty	Audit	Audit-Business	Analyst	26-May-2014	Secondee-Outward-Without Pay	05-May-2013	08-Jul-2014	1
19528	Vig, Minty	Audit	Audit-Business	Senior Audit Associate	26-May-2014	Secondee-Outward-Without Pay	30-Sep-2013	08-Jul-2014	1
19528	Vig, Minty	JV-Audit	KRC-Audit-Asst	KRC-Audit-Asst.Analyst	26-May-2014	Confirmed Staff	26-May-2014	16-Jan-2015	1
19528	Vig, Minty	JV-Audit	KRC-Audit-Asst	KRC-Audit-Asst.Audit Assistant	26-May-2014	Confirmed Staff	26-May-2014	16-Jan-2015	1

@

[tresesco]

Maximum for every Function? If so, try like:

Dimensions : Emp No, Emp Name, Function

Exp:

FirstSortedValue(ProfictCentre, -[Assignment End Date])

FirstSortedValue(Position, -[Assignment End Date])

...

for all the rest fields..

[sharankv]

Hi Tresesco,

The above solution seems not working.. Can you please post a .qvw app fulfilling the requirement with the data above.

It would be of great help to me.

Regards

Sharan

[tresesco]

Help me by sharing your qvw where you tried the solution I suggested and let me work on that.

[sharankv]

Hi Tresesco,

Attached the QVW in the original post.

Please find it.

Thanks

Sharan

[tresesco]

Your date field is a text field. That is why it was not working. I have used an alternative solution using date#(). PFA

[Anonymous]

Sharan,

Add the below code

LOAD [Employee Number],
[Employee Name],
Function,
[Profit Centre],
Position,
[Termination Date],
TerminationsEmployeeData.EmpCategory,
[Assignment End Date],
[Last Update Date]
FROM
[..\Community001.xlsx]
(ooxml, embedded labels, table is Sheet1);
Left Join (Sheet1)
load max([Assignment End Date]) AS Maxdate, [Employee Number] Resident Sheet1 Group By [Employee Number]; 

then create a straight table with all the dimensions  and give expression as

=if([Assignment End Date]=Maxdate, 1)