Finding a match

[mphekin12]

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!

[#2]

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?

[#3]

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.

[mphekin12]

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

[mphekin12]

Thanks for the suggestion!

[#6]

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 .

[mphekin12]

Thank you!