multifield lookup

Question

Hi,

I am trying to do a multiple field lookup in the designer. The data I am processing has 4 fields and 3 of the should match either condition in a Ref table.

Ref Table(sample rows):

Col ACol BCol CABCADC BEA CAD   C

I need compare Col A, Col B, Col C so that it  matches  (Col A and Col B and Col C) for (row1 or row2 or row 3 or ...). Eg:

my filter should have something like (Col A='A' and Col B='B' and Col C='C') or (Col A='A' and Col B='D' and Col C='C') or (Col A='' and Col B='B' and Col C='E') and so on.

Problem here is I have to check for atleast 150 rows and it may increase. So instead of doing the row by row compare for multiple fields as shown above, is there an alternative? (like a loop through for all fields?)

TIA

RolandSchubert · Answer

Hi @Yuri24 ,

what about using a Join tool? You could create a Text Input tool with a list of criteria  and join it to your data. If a col has to be empty, place an '-' in your criteria list, and use a Multi Field Formula tool a create the field to check (including a '-' as replacement for empty cells).

What do you think?

Best,

Roland

MultipleLookup.yxmd

Yuri24 · Answer

Hi Jean,

That's just sample lookup data but the actual data has different values to choose from and I may have to add a few additional look up fields at sometime. And the values are random to work the other way around.

Thanks

Jean-Balteryx · Answer

Hi @Yuri24 ,

Would it be easier going the other way and using rules for what you don't want ?