Forum Discussion

OS_Pizza's avatar
OS_Pizza
Contributor III
2 years ago

Remove suffix from source data where data is coming from a table.

Source

A10000_0

Target

A10000

How can we achieve it in different ways?

1. Connector SQL query -> Using this in the sql query Select left(Account, len(Account)-2) leaves blank rows in the import step.

2. Data Source ?

     2.a. Logical expression

             Return args.Value.Replace("_0", String.Empty) - This works fine.

     2.b. What about Text Fill settings or Substitution settings?

3. Transformation Rule ?

 

Data Sources
  • DanielWillis's avatar
    DanielWillis
    2 years ago

    It shouldn't. Is it something like you're not naming your column when using the function? E.g., should perhaps be: Select left(Account, len(Account)-2) AS Account

  • ChrisLoran's avatar
    ChrisLoran
    Valued Contributor
    2 years ago

    Or use SQL REPLACE function  ,   REPLACE(MyColumn,'_0','')

    • OS_Pizza's avatar
      OS_Pizza
      Contributor III
      2 years ago

      Using functions in Select statement makes the data disappear in the import step 😞

  • ChrisLoran's avatar
    ChrisLoran
    Valued Contributor
    2 years ago

    Here's an example of using REPLACE in a SELECT statment where data does not disappear:

    I am pretty sure this result set would appear in the import step just as it appears in the query result list.
    Could you be more specific on what is meant by "the data disappears" ?

    • OS_Pizza's avatar
      OS_Pizza
      Contributor III
      2 years ago

      It works fine in the SQL query but when you use it in the connector rule , the data doesnt show up in the RegisterID column.

       

      • DanielWillis's avatar
        DanielWillis
        Valued Contributor
        2 years ago

        It shouldn't. Is it something like you're not naming your column when using the function? E.g., should perhaps be: Select left(Account, len(Account)-2) AS Account

  • OS_Pizza's avatar
    OS_Pizza
    Contributor III
    2 years ago

    Absolutely ! I have missed that , Thanks DanielWillis ChrisLoran . This will save a lot of time by discarding complex expression used at Source Dimension. Better to clean data at source then at mapping.

    One more question. Do you happen to know about -

    1. Text Fill settings or Substitution settings. What is the purpose of these two?

     

     

     

    • DanielWillis's avatar
      DanielWillis
      Valued Contributor
      2 years ago

      Hi Pizza. It's generally accepted best practice to mark a topic as solved and ask any new questions in a new thread to ensure others can benefit from your experiences.