topic Re: Help in removing the records in QlikView

Help in removing the records

sharankv — Thu, 07 Jun 2018 06:09:27 GMT

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

Re: Help in removing the records

tresB — Thu, 07 Jun 2018 06:16:59 GMT

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..

Re: Help in removing the records

sharankv — Thu, 07 Jun 2018 06:26:57 GMT

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

Re: Help in removing the records

tresB — Thu, 07 Jun 2018 06:29:17 GMT

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

Re: Help in removing the records

sharankv — Thu, 07 Jun 2018 06:54:22 GMT

Hi Tresesco,

Attached the QVW in the original post.

Please find it.

Thanks

Sharan

Re: Help in removing the records

tresB — Thu, 07 Jun 2018 07:10:42 GMT

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

Re: Help in removing the records

Anonymous — Thu, 07 Jun 2018 07:13:34 GMT

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)