Forum Discussion
Post ProdigyLookUp function ?
Sorry for not being clear. The table in my previous reply was an example of a lookup table that would return an error.
From the MSDN website, about the behaviour of LOOKUPVALUE in case of duplicates:
"If multiple rows match the search values and in all cases result_column values are identical then that value is returned. However, if result_column returns different values an error is returned."
In other words, you are trying to answer the question whether Jan is employed. LOOKUPVALUE will work fine if it finds the same answer twice, but will return an error if finds two different answers.
So, the main issue is you have ambiguity in your real data, and the first question is how you want to handle this ambiguity.
A business rule to solve this issue could be the following: employee X must be considered employed as long as there is no record in Verzuimloop where Employed = 0.
In that case, you could filter Verzuimloop accordingly and check that LOOKUPVALUE returns BLANK.
Post ProdigyI found the formula, but with my current table if gives an error:
A table of multiple values was supplied where a single value was expected.
That error is right because in the table where im searching the names aren't unique so how do i fix this problem ?
Post ProdigyDoes anyone know how to fix this problem ?
Any help is welcome
LaurentCouartouSolution Supplier
You can try and use the TOPN or SAMPLE functions.
Note the TOPN may return more than one value depending on the sorting criteria used.
- RvdHeijden
When i check the discription of both formulas i dont see why i would use them in this case.
I want to check if a person is 'employed' yes or no in a table where the name isn't unique.
i have one table where the names are unique and already have a few calculated colums such as 'Times called in Sick' and 'Total Sickdays'
Now i want another column that checks if he is still employed, i tried to fix this using the relationships but i can't get one doing due to ambiguity
- LaurentCouartou
I thought you were having trouble with the search_value argument or your LOOKUPVALUE expression (where you would pass many values when only one was expected). In that case, you could wrap the search_value (column) expression in a TOPN or a SORT expression to make it only returns one value.
If, however, the error comes from the LOOKUPVALUE finding many matches for the input criteria, then neither of these functions will help. You would have to rewrite your expression to make certain it only returns one value.
Depending on your lookup table, searching on several columns may solve the issue.
If you only need to perform an existence check, you may also do without LOOKUPVALUE and simply evaluate a COUNTROWS with the appropriate filter context.