Vendor File - Address Match

[gfisch13]

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

[Nicole-Smith]

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?

[gfisch13]

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.

[veidlburkhard]

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

[gfisch13]

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 

[gfisch13]

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!

[veidlburkhard · Accepted solution]

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

[gfisch13]

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.

[gfisch13]

Thank you again!!  Now that I understand more I see how this works and will be using the function more in the future.