Select first row of a grouped dimension in load script

[#1]

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 · Accepted solution]

Hi

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

[#3]

Hi Jonathan,

It works!

Thank you very much for your kind attention

Regards,

Khairul