How to join data sources when matching fields are spread across multiple columns.

[im]

Hi

One table contains a number that is a match for a number in one of four columns in another table - with information I'm missing in the first table.

What's the best way to connect the two pull information from the second table?

A series of ifs?  

Thank you

[Edvin]

I guess WildMatch() would do the trick.
This one is an good example on how to use it: https://community.qlik.com/t5/New-to-QlikView/how-to-use-wildmatch/td-p/1404734

[JordyWegman]

Hi Im,

Can you give an example file? 

If I'm understanding correctly, you don't know upfront in which column the right data is? For being able to join, you should probably try to fix this. 

If this is not possible, you can try to make a concatenated key:

Column1 & '|' & Column 2 & '|' & Column 3 & '|' & Column 4 as %Key

And then do a wildmatch on your key.

Jordy

Climber

[im]

Hi Jordy

That's how my tables look. The column number can contain any of the four numbers below and I need the product.

I'm still mostly using data manager but It doesn't seem like there's a good solution without load script.

Thanks

[JordyWegman]

Hi Im,

Seeing this, you can better un-pivot your second table. You can do this in the Data Manager, see this video. 

Rename this column with all your numbers to Number. This way it is connected with the customer table. Rename the column with stock/vendor etc. to product type number.

Jordy

Climber