Forum Discussion
Helper IWildcard matching & returning a value
Hi,
Basically, I have a table named "people" with different records and a record named "ref"
id | country | age | ref
1 | China | 25 | a32f134fnaq?ref=site
2 | USA | 22 | a32f134fnaq
3 | China | 28 | 31444cp2x?ref=google
4 | China | 33 | a32f1dddd?ref=none
And another table named "refs"
id | name | ref
1 | john | a32f134fnaq
2 | jack | 31444cp2x
3 | sam | a32f1dddd
A column has been created (assume it's called "ref_name" that uses VLOOKUP and returns the "name" column from "refs" by looking it up according to "ref" from the table "people".
Obviously, it does not work as there are parameters (after the ?), and it would only work if the inputs are precise (e.g. the bold record).
How can I manage to overcome this? So that even if there is something like "31444cp2x?ref=google", it will check if "ref" in "refs" conatains 31444cp2x and return 'name' (aka approximate match or something like that).
Try this one
ref_name = MINX ( FILTER ( refs, SEARCH ( refs[ ref], People[ ref], 1, 0 ) > 0 ), [ name ] )
7 Replies
- Zubair_Muhammad
Community Champion
Try this column
ref_Name = VAR TextbeforeQM = LEFT ( People[ ref], VAR mylen1 = FIND ( "?", People[ ref], 1, 0 ) RETURN IF ( mylen1 = 0, LEN ( People[ ref] ), mylen1 - 1 ) ) RETURN LOOKUPVALUE ( refs[ name ], refs[ ref], TextbeforeQM )
analystictThanks for your reply :)
Thing is, "?blablabla" is not always the case.
It could also be ref=facebook?a32f134fnaq
or
a_id=a32f134fnaq
Is there a workaround for that?
Thanks :)
- Zubair_Muhammad
Try this one
ref_name = MINX ( FILTER ( refs, SEARCH ( refs[ ref], People[ ref], 1, 0 ) > 0 ), [ name ] )