I would like to join a transactional data source table with a master data source table.
In the master data table, the key field "CUSTOMER" is ALPHA converted (leading zeros). In the transactional data table, the key field "CUSTOMER" is not ALPHA converted (w/o leading zeros).
How can I handle such scenarios if I have a key field with and w/o leading zeros. Is it´s possible to cut the leading zeros with the help of a formula or is it`s possible to convert the format from ALPHA-converted to not ALPHA-converted (w/o leading zeros)?
Source table 1 (fact table) with transactional data:
Customer ID w/o leading zeros:
Source table 2 (dimension table) with customer master data each:
Customer ID with leading zeros (with the exception of customer C5300):
Star join with LEFT OUTER 1:1 JOIN between fact table and dimension table:
It´s not possible to bring the customer master data of the customers 5000, 5100 and 5200 to the output because the customer no. ID are not unique between the both source tables (customer no. id´s w/o leading zeros vs. leading zeros)
How can I solve the Issue?
Many thanks in advance!