topic Finding a match in QlikView

Finding a match

mphekin12 — Wed, 03 Apr 2013 15:12:14 GMT

Hello,

I'm looking for some suggestion on how to solve a problem with QlikView. I have a table (tblLookup) with two columns (Item Number and UPC Number). I need to look in another table (tblProduct) to see if either of these columns match the Vendor Product Number column in tblProduct.

In other words I am looking to see if the Item Number exists in the Vendor Product Number field, if not, does the UPC Number exist in the Vendor Product Number field.

Any suggestions on how to accomplish this?

Thank you in advance!

Re: Finding a match

Wed, 03 Apr 2013 16:26:41 GMT

One suggestion.

Use concat function on vendor product table and concat all the produt number field and place this in one temp table.

When you are lookinto to tblLookup just use substring function to check if tblProduct exists in the comma separated list or not if yes use flag=1(this means product exists else 0) similarly you do it for UPC number.

makes sense?

Re: Finding a match

Wed, 03 Apr 2013 18:06:37 GMT

There is a function called Match() in QlikView, which is similar to the vLookup in Excel. Find out more on how to use this here. There is also a You tube video on this here.

Re: Finding a match

mphekin12 — Wed, 03 Apr 2013 18:16:55 GMT

Thank you for the tip. I also found that the Exist function will work.

Re: Finding a match

mphekin12 — Wed, 03 Apr 2013 18:41:40 GMT

Thanks for the suggestion!

Re: Finding a match

Wed, 03 Apr 2013 19:11:45 GMT

If we consider 2 tables .

1. tblProduct : fields ( VendorProductNumber ).

2. tblLookup : fields ( ItemNumber , UPCNumber )

Here by using this code you can find which field values got match and not match with the VendorProductNumber field values .

Output:

LOAD

if(Lookup ('ItemNumber', 'ItemNumber', VendorProductNumber, 'tblLookup')<>Null(),Lookup('ItemNumber', 'ItemNumber', VendorProductNumber, 'tblLookup'),'NoMatch') as IdItemNumberMatch,

if( Lookup ('UPCNumber', 'UPCNumber', VendorProductNumber, 'tblLookup')<>Null(),Lookup ('UPCNumber', 'UPCNumber', VendorProductNumber, 'tblLookup'),'NoMatch') as IsUPCNumbermatch

Resident tblProduct;

Drop table tblLookup ;

Hope It helps you .

Re: Finding a match

mphekin12 — Wed, 03 Apr 2013 19:26:12 GMT

Thank you!