Skip to Content
avatar image
Former Member

How to join tables in case of key fields are ALPHA-converted (leading zeros)

Hello experts,

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)?

Example scenario:

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!

Best regards,

1.png (3.3 kB)
2.png (3.6 kB)
3.png (31.3 kB)
4.png (13.4 kB)
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Best Answer
    Aug 06, 2017 at 05:34 AM

    The easiest way would be to remove the leading zeros from the ALPHA input converted values, because the other way around - when you wanna add leading zeros - you have to do some more checks (e.g. checking if the value is not alphanumeric ...).

    You can remove the leading zeros with the LTRIM function in a calculated column. E.g.

    LTRIM("CUSTOMER", "0")

    Of course for performance reasons it would be better to have the values already in the same format in your sources.


    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      Hello Florian,

      thank you. I´ve added a new calculated column to remove the leading zeros.

      Now the join works as aspected.


      5.png (30.8 kB)
      6.png (18.6 kB)