add leading zero

meadow_ryan
meadow_ryan Member
in Magic ETL

Is it possible to add a leading zero to a field if it has a specific number of digits....problem I'm having is I have a field "routing number" which for the records that have 9 digits it displays all the numbers but the ones which have 8 digits it removes the leading zero.

 

thanks

Comments

  • AS
    AS Coach

    Is your column a number instead of a string?  It sounds to me like it's a number and they always drop leading zeros.  

    Can you bring the column in as a string instead?  It's probably best to do that before Domo gets it for the first time.  If you can convert the data type upfront, I think that's your best bet.

    In case that's not an option, you can do some beast modes to get what you want.

     

    Turn the number into a string and do some conditional logic by finding the string length, and if it's 8 characters, then concatenate a zero on to the front.

     

    CASE

    WHEN LENGTH(CONCAT('',`column`)) = 8

    THEN CONCAT('0',`column`)

    ELSE CONCAT('',`column`)

    END

    Aaron
    MajorDomo @ Merit Medical

    **Say "Thanks" by clicking the heart in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • meadow_ryan
    meadow_ryan Member

    so the column was a decimal and I changed to text by using the set column type to text but it still isn't showing the leading zeroes

  • AS
    AS Coach

    You set the column type to text within a Domo dataflow?  That will just take a digit of zero and turn it into a character of 0, not add zeros.  Were you able to try fixing the datatype outside of Domo first?

    Aaron
    MajorDomo @ Merit Medical

    **Say "Thanks" by clicking the heart in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • meadow_ryan
    meadow_ryan Member

    problem I'm seeing now is that its not just ones with 8 digits, any of the bank_id field which as a leading zero is getting removed.

  • ST_-Superman-_
    ST_-Superman-_ Coach

    You can use LPAD() in a data flow.

    You can use this with text or value fields.1.png

     


    “There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
  • AS
    AS Coach

    I was just about to suggest LPAD.  Solving it on the data source side is the best solution, but LPAD is what I used most often.  But we always use SQL dataflows, and it sounds like you might be using the Magic ETL version, which doesn't have that function that I'm aware.

    Aaron
    MajorDomo @ Merit Medical

    **Say "Thanks" by clicking the heart in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • meadow_ryan
    meadow_ryan Member

    In here? What is LPAD()

  • meadow_ryan
    meadow_ryan Member

    I can add it to the sql data flow but how do I write it to select all columns but do lpad() on bank_id field?

     

    thanks

  • ST_-Superman-_
    ST_-Superman-_ Coach

    12.png

     


    “There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
  • AS
    AS Coach

    Good hack. Nice!

    Aaron
    MajorDomo @ Merit Medical

    **Say "Thanks" by clicking the heart in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • meadow_ryan
    meadow_ryan Member

    So I've done the first part but where do I add the second string operation....see attachment

  • AS
    AS Coach

    Add a replace text tile next after the string operation.

    Aaron
    MajorDomo @ Merit Medical

    **Say "Thanks" by clicking the heart in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • meadow_ryan
    meadow_ryan Member

    so I have it as the attached where I first set my column type for bank_id to text which was decimal then run the string operation LPAD contains bank_id padded to length: 10 from side: left, then did the replace text LPAD with a space in the term to search for and replace with 0, but still isn't adding the leading zero....thanks for all the help so far appreciate it.

  • ST_-Superman-_
    ST_-Superman-_ Coach

    can you share a screen shot of the settings for each step?  


    “There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
  • meadow_ryan
    meadow_ryan Member

    images attached...thanks again

  • ST_-Superman-_
    ST_-Superman-_ Coach

    From looking at your first post, I think you should change the length to 9.

     

    From my understanding, you are getting a value of 071000013 but Domo is dropping the leading zero to be just 71000013.  

    21.png

     


    “There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
  • ST_-Superman-_
    ST_-Superman-_ Coach

    Just to confirm, the field that should have the 9 digits will be whatever you choose to name the new column in the settings for that step (in your screenshot `LPAD`)

     

     


    “There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
  • meadow_ryan
    meadow_ryan Member

    so I changed the length to 9 but am seeing that all of the rows won't have a bank_id with 9 digits, ex. one of the id's is 123456 which is correct but the LPAD is adding 000 before it

     

    The rows which are 8 digits and are missing the leading zero are correct now

  • ST_-Superman-_
    ST_-Superman-_ Coach

    Correct.  Using the PAD function will force all of the fields to have the specified number of characters.  Is there another field in your data set that would indicate that the bank_id should not have 9 digits? You could filter those out before using the Pad function and the join the two back together.


    “There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
  • meadow_ryan
    meadow_ryan Member

    at first glance it looks like there are just 3 rows which have bank_id's with less than 8 digits

  • ST_-Superman-_
    ST_-Superman-_ Coach

    If the number is that small, I would recommend filtering out those values first.  Here are the steps you would need to add:

    First, add a filter before the "String Operation" where we created the LPAD field1.png

     

    2.png

     

    Second, you need to apply the opposite filter so that you only include the three values that don't need 9 digits:3.png

     

    4.png

     

    (make sure that you change the setting to include rows that meet "any" of the rules

     

    Third, you need to add a column to the bottom data table that matches the name you created earlier (LPAD in your case).  I used "Text_pad" in my sample.  It just needs to be the same name.5.png

     

     

    Fourth, use "Set Column Value" to map the values from your bank_id field to the new field:6.png

     

     

    Fifth, Append the two data tables:7.png

     

     

    Then your output will look something like this:8.png

     

     


    “There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
  • meadow_ryan
    meadow_ryan Member

    so I believe the card is correct now, problem I'm running into now is the data in this table has to be exported to a CSV, which is then dropping the leading zeroes.

  • cwolman
    cwolman Contributor

    Are you opening the csv file in excel?  Excel drops the leading zeros.  Open the file in notepad or another text editor and you should see the leading zeros in your csv file.


    -----------------
    Chris
  • jamyoung
    jamyoung Member

    Nice solution @ST_-Superman-_. If you want to standardise the number of characters, but your string length (@official_id in my example) is unknown, you can take the following extra steps:

    Screenshot 2019-09-05 at 00.33.22.png

     

    1. STRING OPERATION to add large number of leading spaces to left hand side.

    Screenshot 2019-09-05 at 00.26.30.png

    2. REPLACE TEXT (RegEx) to change the spaces to 0s.

    Screenshot 2019-09-05 at 00.30.39.png

    3. STRING OPERATION to trim the number of characters you desire from the right hand.

    Screenshot 2019-09-05 at 00.27.10.png

    4. (Optional) SET COLUMN VALUE to overwrite the original value.

    Screenshot 2019-09-05 at 00.27.18.png

     

Categories