A VLOOKUP is a quick way to enrich large amounts of records by means of an import source. It is similar to a create/change database rule, but allows for a far more extensive reach within your data.
The functionality is similar to the VLOOKUP formula in an EXCEL document and is triggered by importing a new file into an import source. With the VLOOKUP you can add a reference table to your import sources to which a project can refer and based on a reference key, fill all desired background variables.
Now how do you set up a correct VLOOKUP rule?
First create an import source to serve as a reference table and set the structure of definitions (=columns) that exist in the reference file.
Next, go to the rule section of your project database and select if you would like the VLOOKUP to run over Created record or over Mutated Records.
Give the file a name, and set a condition for executing the record.
Choose the action “VLOOKUP”
First select which column in you project datafile you want to run the VLookup over. (the column that needs to match the key-column in your reference table)
Then select the dataset you would like to match it against (either by importslot or a different project), and determine which column within that reference file will serve as a reference key, that holds the data matching the lookup column.
Once you’ve the determined the key and the reference key, you are able to select which column in your project should be filled with what data from your reference file according to the reference key. In other words which data from your reference file should be mapped to which column in you project database.
Always click on the plus sign after having chosen the column to add them to your VLookup execution.
Click save to save the lookup rule.
Now, if the SAP number that is filled out in the questionnaire matches a number as presented in the reference table, all allocated columns in your project will be filled according to the reference table.
This prevents the respondent from filling out a lot of information that can easily be automated, making your gatered less prone to manual mistakes.