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 A | Col B | Col C |
A | B | C |
A | D | C |
B | E | |
A | C | |
A | D | |
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
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
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