Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save $300 before February 6: REGISTER NOW!
cancel
Showing results for 
Search instead for 
Did you mean: 
gfisch13
Creator II
Creator II

Vendor File - Address Match

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

Labels (1)
1 Solution

Accepted Solutions
veidlburkhard
Creator III
Creator III

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

View solution in original post

8 Replies
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
Creator II
Creator II
Author

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
Creator III
Creator III

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

ResultTable.jpg

Hope this helps

Burkhard

gfisch13
Creator II
Creator II
Author

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
Creator II
Creator II
Author

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
Creator III
Creator III

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
Creator II
Creator II
Author

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
Creator II
Creator II
Author

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