topic Re: Vendor File - Address Match in QlikView

Vendor File - Address Match

gfisch13 — Sat, 16 Nov 2024 01:25:32 GMT

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

Re: Vendor File - Address Match

Nicole-Smith — Fri, 24 Jan 2020 21:51:18 GMT

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?

Re: Vendor File - Address Match

gfisch13 — Sat, 25 Jan 2020 14:33:10 GMT

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.

Re: Vendor File - Address Match

veidlburkhard — Sun, 26 Jan 2020 18:43:09 GMT

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

Re: Vendor File - Address Match

gfisch13 — Mon, 27 Jan 2020 14:14:40 GMT

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

Re: Vendor File - Address Match

gfisch13 — Mon, 27 Jan 2020 15:17:27 GMT

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!

Re: Vendor File - Address Match

veidlburkhard — Mon, 27 Jan 2020 16:34:13 GMT

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

Re: Vendor File - Address Match

gfisch13 — Mon, 27 Jan 2020 17:43:06 GMT

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.

Re: Vendor File - Address Match

gfisch13 — Fri, 31 Jan 2020 17:43:48 GMT

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