topic Filtering Records from QVD Load in QlikView

Filtering Records from QVD Load

Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Filtering Records from QVD Load

sunny_talwar — Mon, 02 May 2016 15:42:08 GMT

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

Re: Filtering Records from QVD Load

MarcoWedel — Mon, 02 May 2016 22:14:16 GMT

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