Filtering Records from QVD Load

[#1]

Hi, I am new to Qlikview and do not have much coding background. I have a master qvd file that contains data similar to table below :

Table : Emp_Sign_Record
Id	Staff No	Sign Off Date	Task ID
3	A	6-May-16	FS -00556
4	A	7-May-16	FS -00259
5	C	10-May-16	FS -00637
6	B	11-May-16	MZ -00772
7	B	11-May-16	GT -00234
8	A	11-May-16	FS -00201
9	C	12-May-16	GT -00548
10	A	12-May-16	FS -00869

I am trying to load only details for the last record for each staff so that only the following is loaded but is having all kinds of issues. Would it better to load Table 1 and create table 2 as a resident table?

Load Results : Emp_Last_Sign
Id	Staff No	Sign Off Date	Task ID
10	A	12-May-16	FS -00869
7	B	11-May-16	GT -00234
9	C	12-May-16	GT -00548

Appreciate some help from anyone here. Thanks.

[sunny_talwar]

May be try like this:

TableName:

LOAD FirstSortedValue(Id, [Sign Off Date]) as Id,

          [Staff No],

          Date(Max([Sign Off Date])) as [Sign Off Date],

          FirstSortedValue([Task ID], [Sign Off Date]) as [Task ID]

FROM YourQvdFile.qvd (qvd)

Group By [Staff No];

Alternatively, you can also do this:

TableName:

LOAD Id,

          [Staff No],

          [Sign Off Date],

          [Task ID]

FROM YourQvdFile.qvd (qvd);

Right Join(TableName)

LOAD [Staff No],

          Date(Max([Sign Off Date])) as [Sign Off Date]

Resident TableName

Group By [Staff No];

[MarcoWedel]

Hi,

one solution could be also:

table1:
LOAD * FROM [https://community.qlik.com/thread/215181] (html, codepage is 1252, embedded labels, table is @1, filters(Remove(Row, Pos(Top, 1))));

table2:
LOAD *, [Staff No] as TempFld
Resident table1
Where not Exists (TempFld,[Staff No])
Order By [Sign Off Date] desc;

DROP Table table1;
DROP Field TempFld;

hope this helps

regards

Marco