topic Re: Select first row of a grouped dimension in load script in QlikView

Select first row of a grouped dimension in load script

Tue, 02 Dec 2014 04:59:42 GMT

Hi guys,

Consider the following script:

Load

VendorId,

EntryNo,

Date,

DocumentNo

Resident tbl_Vedors

Order By VendorId asc,

EntryNo asc,

Date asc;

and the result is as follows

VendorId	EntryNo	Date	DocumentNo
ABC001	1234	03/01/2014	DOC_A_01
ABC001	1234	03/01/2014	ABB003
ABC001	1234	04/01/2014	ACC090914
ABC001	1235	04/01/2014	INV-001-222

....

My question is, how should I do it in load script if I want to get only the first row of every VendorId-EntryNo grouping (in yellow) ?

Thank you for your kind attention guys

Regards,

Khairul

jonathandienst — Tue, 02 Dec 2014 05:11:45 GMT

This should do it:

Load

VendorId,

EntryNo,

VendorId & '-' & EntryNo As Key,

Date,

DocumentNo

Resident tbl_Vedors

Where Not Exists(Key, VendorId & '-' & EntryNo)

Order By VendorId asc,

EntryNo asc,

Date asc;

HTH

Jonathan

Tue, 02 Dec 2014 05:39:22 GMT

Hi Jonathan,

It works!

Thank you very much for your kind attention

Regards,

Khairul