Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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.
Is a Load Script technique OK?
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.
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".
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.
For some reason I cannot see the attachment . Could you please re attach?
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.
Thanks, I was able to download the attachment.
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 |