Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All - I'm working with a purchase order (PO) file that includes shipping addresses to both home and office locations. I have a second file that includes office location addresses with an indicator of 'O' in a separate field.
I'd like to join these so that the shipping addresses in the PO would have the indicator field with the O attached to the record so that I can differentiate shipments made to office locations and home locations.
I was thinking a Left Join is what I need, but I'm confusing logic from some other software applications I've used. I've simplified the tables and field names below, think I just need help understanding the logic. Thanks!!
George
POTable:
DLV_ADR_TXT as Key
LocationsTable:
Address as Key
Indicator
Hi Gfisch13,
the same solution can be obtained with a mapping table, which is not so resource consuming compared to a join.
The script would look like this:
MapIndicator:
Mapping
LOAD Address,
OfficeInd
FROM
[LeftJoinTest.xlsx]
(ooxml, embedded labels, table is Locations);
T1:
LOAD PO_ID,
DLV_ADR_TXT,
DESC,
ApplyMap('MapIndicator', DLV_ADR_TXT, '') as OfficeInd
FROM
[LeftJoinTest.xlsx]
(ooxml, embedded labels, table is PO);
This should also solve the problems with your double rows...
Regards
Burkhad
A left join does sound like what you need.
If you'd like further help, can you please post some example data along with expected output?
Nicole, thank you! Here is some test data. I really appreciate the help, I learn alot from these exercises with the pro's!
Data attached.
Hi Gfisch13,
what Nicole-Smith was proposing ist certainly right:
the script
T1:
LOAD PO_ID,
DLV_ADR_TXT,
DESC
FROM
[LeftJoinTest.xlsx]
(ooxml, embedded labels, table is PO);
Left Join(T1)
LOAD Address as DLV_ADR_TXT,
OfficeInd
FROM
[LeftJoinTest.xlsx]
(ooxml, embedded labels, table is Locations);
The result table
Hope this helps
Burkhard
Burkhard - thank you for the reply. This worked so I decided to try it on another table and while that worked as well there is a noticeable reduction in performance. Am i forcing too many items through memory? Is there a more efficient way to load/store this data?
Thanks, George
One other issue I'm noticing is that there are now two records for each address and only one is tagged with the indicator I showed above? Is there something else I need to do?
Thanks!
Hi Gfisch13,
the same solution can be obtained with a mapping table, which is not so resource consuming compared to a join.
The script would look like this:
MapIndicator:
Mapping
LOAD Address,
OfficeInd
FROM
[LeftJoinTest.xlsx]
(ooxml, embedded labels, table is Locations);
T1:
LOAD PO_ID,
DLV_ADR_TXT,
DESC,
ApplyMap('MapIndicator', DLV_ADR_TXT, '') as OfficeInd
FROM
[LeftJoinTest.xlsx]
(ooxml, embedded labels, table is PO);
This should also solve the problems with your double rows...
Regards
Burkhad
This is not really working as I was thinking it would and after reviewing the code I understand why I'm getting the result I got. I really wanted the Indicator to be the contents of the 'OfficeInd' field, rather than the address itself.
The Join was getting me closer to where I wanted to be but the performance was not desirable. I'm attaching my full script so you can see how I'm using my data. Hopefully that will focus my needs a bit more??
Metaddress is my indicator(OfficeInd) field.
Appreciate the learning opportunity.
Thank you again!! Now that I understand more I see how this works and will be using the function more in the future.