cancel
Showing results for 
Search instead for 
Did you mean: 
Reply

Re: LookUp function ?

LOOKUPVALUE did not return any error on your data because duplicate entries all return the same results.

 

It would not work if, for example, with the following data:

 

nameemployed
Jan1
Jan0

 

What is your business rule to handle inconsistencies?

 

 

Highlighted
RvdHeijden Member
Member

Re: LookUp function ?

@LaurentCouartou

I dont understand what you are saying, the lookupvalue does give an error.

I understand what your saying but my tables aren't made up that way

 

In this table the names are unique but in the table where i want the lookupvalue to look the names aren't unique

I thought this would be a simple question but it seems harder then i thought

 

2017-04-18_1157.png

Re: LookUp 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.

RvdHeijden Member
Member

Re: LookUp function ?

@LaurentCouartou

you were right, i noticed there were different values when i checked the data.

I thought those values were automated from our system but apperantly someone has to fill them manually hence the different values.