I am using Data Services XI 3.2, i wanted to perform LOOKUP on City database of USA country against the data that i have in my Excel spread sheet.
I have followed the following procedure.
1. In my job, i have taken 2 source table, 1 as CITY database of USA and 2 City and other information containing spreadsheet.
2. used join transform, created the new output column called 'CITY', used the LOOKUP_EXT function in Mapping Tab and performed the following query.
lookup_ext(Database_name.DBO.city_zip_lookup_table,'PRE_LOAD_CACHE','MAX],[CITY],[NULL],[CITY, '=', excel_spreadsheet.city]')
By using this standard function I am not getting the expected output.
I want my output to be..
In the newly created CITY table, the output should be coming from the 'CITY_ZIP_LOOKUP_TABLE'.
It should validate the city value from excel spreadsheet with the CITY_ZIP_LOOKUP_TABLE and populate the value from lookup table.