Merging two data sets by using data enrichment (lookup) can be achieved using two ORQA operations:
- Create Dictionary from Table
- Enrich Table from Dictionary.
For example let’s consider the two data sets below:
Employees
Employee,Position
Anna,Senior Sales Clerk
Joe,Sales Clerk
Sophie,Sales Clerk
Dave,Junior Sales Clerk
Sales
Employee,Sale_$,Department
Anna,399,Kitchen
Anna,200,Kitchen
Anna,450,Lounge
Dave,20,Kitchen
Dave,199,Kitchen
Sophie,1000,Kitchen
Sophie,400,Kitchen
Joe,20,Lounge
Joe,499,Lounge
We want to incorporate the Position of the Employee into the Sales report so the columns within the new Enriched Sales Report will be Employee, Sales_$, Department and Position. The following steps show how to achieve this:
1) Read Employees Excel File into Employees Table
2) Create Dictionary from Table
This operation will create a dictionary using the supplied column of a data stream passed to it. In this example we are using the Employee column from the Employees table we have just read in above.
This will produce a dictionary, empDict, in the following format:
Key Value
--- ---------
Joe {Employee=Joe, Position=Sales Clerk}
Sophie {Employee=Sophie, Position=Sales Clerk}
Dave {Employee=Dave, Position=Temp Sales Clerk}
Anna {Employee=Anna, Position=Senior Sales Clerk}
3) Read Sales Excel File into Sales Table
4) Enrich Table from Dictionary
This operation will map certain columns of the stream passed to it with a dictionary provided. In this example the Table is Sales. The Key Column is the Employee column within the Sales table as that is the value we are matching to the key within empDict. The Value Column is Position as that is the value from empDict that we want to enrich our Sales table with. The Dictionary is empDict and we are storing this new Table as EnrichedSalesReport.
The output of running this task will be the new EnrichedSalesReport that has the new Position column enriched from the Employees table:
