Top 1 record from the group of records

[#1]

I have the information in two separate tables and need the Ref # to be picked from Table 2 , on the basis of maximum Ref Date/Time and Time for the Product and Serial Combination where the Ref Date/Time is less than Date/Time.  Please see the desired output below.  Kindly suggest how it could be possible. Thanks !

Table 1

Product	Serial #	Container #	Qty	Date/Time
XYZ	565A	TP56	3	1/25/15 1:20 PM
ABC	342P	TP56	2	1/20/15 4:20 PM

Table 2

Product	Serial #	Container #	Qty	Ref #	Ref Date/Time
XYZ	565A	LP32	1	789879KP	1/23/15 1:20 AM
XYZ	565A	LP35	1	789879AX	1/24/15 5:30 PM
XYZ	565A	LP39	1	789879TP	1/25/15 11:20 AM
XYZ	565A	LP38	1	789879MN	1/27/15 5:20 PM
XYZ	565A	LP36	1	789879YZ	1/28/15 9:20 PM
ABC	342P	LP39	2	789879KP	1/18/15 1:20 AM
ABC	342P	LP39	2	789879AX	1/19/15 5:20 PM
ABC	342P	LP39	2	789879MN	1/27/15 5:20 PM

Desired Output :

Product	Serial #	Container #	Qty	Date/Time	Ref #
XYZ	565A	TP56	3	1/25/15 1:20 PM	789879TP
ABC	342P	TP56	2	1/20/15 4:20 PM	789879AX

[Peter_Cammaert]

Is a Load Script technique OK?

[#3]

Peter,

Load script for Table 1 and Table 2 seems to be fine. I was wondering how desired output could be possible with subsequent loads.

[Peter_Cammaert]

Then have a look at the document in attachment of my previous post. The load script in the document produces the image in my previous post, which corresponds to your "Desired Output".

[Peter_Cammaert]

If you want to know how to integrate this technique into your own script, copy and paste the MAPPING LOAD and LEFT JOIN statements, and adjust any field naming where needed.

Or post your original script.

[#6]

For some reason I cannot see the attachment . Could you please re attach?

[Peter_Cammaert · Accepted solution]

No, because that won't make a difference.

Open the original discussion Top 1 record from the group of records in your web browser. Do not use your Inbox or a phone or any other device where discussions are shown without attachments.

[#8]

Thanks, I was able to download the attachment.

[#9]

Thanks Peter, I was able to get the data through Mapping Load but in some cases I have been getting the reference as blank. Its happening because the Product Serial # combination doesn't exist in Table 2 , with Ref Date/Time prior to the Date\Time of the record from Table 1.

Is it possible to do a secondary search , where in case Ref # is returned blank then it performs search by Product Container # combination and it picks the Ref #, of the Ref Date/Time prior to the Date\Time of such records.

Table 1

Product	Serial #	Container #	Qty	Date/Time
XYZ	565A	TP56	3	1/25/15 1:20 PM
ABC	342P	TP56	2	1/20/15 4:20 PM
PQR	115C	FX55	1	1/27/15 2:13 PM

Table 2

Product	Serial #	Container #	Qty	Ref #	Ref Date/Time
XYZ	565A	LP32	1	789879KP	1/23/15 1:20 AM
XYZ	565A	LP35	1	789879AX	1/24/15 5:30 PM
XYZ	565A	LP39	1	789879TP	1/25/15 11:20 AM
XYZ	565A	LP38	1	789879MN	1/27/15 5:20 PM
XYZ	565A	LP36	1	789879YZ	1/28/15 9:20 PM
ABC	342Q	LP39	2	789879KP	1/18/15 1:20 AM
ABC	342P	LP39	2	789879AX	1/19/15 5:20 PM
ABC	342P	LP39	2	789879MN	1/27/15 5:20 PM
PQR	457Y	FX55	3	96868TY	1/24/15 1:23 PM
PQR	252Y	AX55	3	45469TY	1/26/15 2:23 AM
PQR	115C	FX55	1	48797GV	1/29/15 2:23 PM

Desired Output :

Product	Serial #	Container #	Qty	Date/Time	Ref #
XYZ	565A	TP56	3	1/25/15 1:20 PM	789879TP
ABC	342P	TP56	2	1/20/15 4:20 PM	789879AX
PQR	115C	FX55	1	1/27/15 2:13 PM	96868TY