Human Capital Management Blogs by SAP
Get insider info on SAP SuccessFactors HCM suite for core HR and payroll, time and attendance, talent management, employee experience management, and more in this SAP blog.
cancel
Showing results for 
Search instead for 
Did you mean: 
pmacgovern
Product and Topic Expert
Product and Topic Expert

Introduction


Lookup tables are used very frequently in both the Compensation and Variable Pay modules to pull non-employee data into worksheets - budget percentages is the most common use, but certainly not the only one. Often times, the Wildcard Character '*' is used in an input coluimn. However, it might not work quite how you think.

System Behaviour


The system documentation indicates the way lookup tables find results:


While this is technically true, it is important that the Wildcard Character doesn't override exact matches - so a wildcard in the first row might be overridden by an exact match in a lower row.

Consider the following lookup table:


if there is a column with the following formula:
lookup("table",job,grade,user,1)

The system will search from top to bottom in column A then when it finds a match for a value in column A it will continue to see if can find an exact match before moving to column B. If it cannot find an exact match, then it uses the first Wildcard row.  Here is the result from using this lookup table:


I would imagine that most consultants, given the data in Sandy Ago's record, would assume that the lookup statement would return "User", as the first row of the lookup table ( *, *, ago1) would appear to match. However, since there is an exact match of Job (MGR-IT) in row 5 (and the rest of the row matches with its wildcards), then that row is pulled from the table. Basically, "MGR-IT, *, *" trumps "*, *, ago1", even though they both technically match.

If you change the table to this:


then you get the following results:



Conclusion


When using wildcards, the order of columns matters. If the first column is to contain wildcards, be careful to put the most "exact" matches at the top of the lookup table.

Credit


Many thanks to Skip Jones for doing the research upon which this blog is based.

 

 
1 Comment