Forum Discussion
Solved
Lookup with wildcards (sequential lookup)
Hi all,
I'm trying to solve the following problem. I have a main table with values in each column. I want to look up an Id from another table.
| Unit | Debtor | Location | Id |
| A | ABCD | AMS | (should be 14) |
| B | ABCD | QUE | (should be 12) |
| C | ABCD | AMS | (should be 14) |
The other table contains Id's with condition columns. However, some of these values are blank, which mean they act as a wildcard/fallback.
| Id | Debtor | Location | Amount |
| 12 | ABCD | 30 | |
| 14 | ABCD | AMS | 40 |
If I use an OR operator || , like main[location] = lookup[location] || ISBLANK( main[location] ), it always returns Id 14 because it will always find a match with the blank [location], and thus returns Id 14.
How can I make this check sequential? So only check the next condition if it doesn't find an exact match first? So in this case, first check the whole lookup table if there's a specific row with [location] AMS, if not then check for blank [location]
- Id =var d = [Debtor]var l = [Location]var f = filter(Table2,[Debtor]=d && coalesce([Location],l)=l)return if(countrows(f)>0,maxx(f,[Id]))
1 Reply
- Id =var d = [Debtor]var l = [Location]var f = filter(Table2,[Debtor]=d && coalesce([Location],l)=l)return if(countrows(f)>0,maxx(f,[Id]))