Member

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

• 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"
• 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

• 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"
• 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.

• Coach

You can use LPAD() in a data flow.

You can use this with text or value fields.

“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
• 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"
• Member

• 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

• Coach

“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
• 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"
• Member

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

• 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"
• 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.

• 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
• Member

images attached...thanks again

• 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.

“There is a superhero in all of us, we just need the courage to put on the cape.” -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
• 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

• 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
• Member

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

• 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 field

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

(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.

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

Fifth, Append the two data tables:

Then your output will look something like this:

“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
• 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.

• 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
• 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:

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

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

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

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