Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On May 18th at 10AM EDT we will answer your QlikView questions live. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
mphekin12
Specialist
Specialist

Finding a match

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!

6 Replies
Not applicable

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?

Not applicable

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
Specialist
Specialist
Author

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

mphekin12
Specialist
Specialist
Author

Thanks for the suggestion!

Not applicable

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
Specialist
Specialist
Author

Thank you!