December 12, 2020
I would like to have some ideas or bets practice on how to do a mapping table to group a transactional table.
here is the situation:
I have a general ledger transaction report and I need to identify the vendor which is in the description column. The description column also contains dates, service, locations etc.
the current process to map the vendor is as follow:
- duplicate the description column
- remove dates and symbols, in order to get an almost unique description.
- export the table to excel and manually add the vendor ID
- add this table to PQ again and merge it with the transaction table and extract the new vendor id column.
The question is if there is a more efficient way to do it, like for example using a list/table of all vendors and do a list contain look up to find the vendor in the description and then add the ID. if this is possible I would like to know how to do it or where can I find a similar case.
Thank you very much
October 18, 2018