add leading zero

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

Find more posts tagged with

Comments

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

meadow_ryan

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

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?

meadow_ryan

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

You can use LPAD() in a data flow.

You can use this with text or value fields.

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.

meadow_ryan

In here? What is LPAD()

Capture.JPG

meadow_ryan

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

Good hack. Nice!

meadow_ryan

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

Capture.JPG

Add a replace text tile next after the string operation.

meadow_ryan

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

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

meadow_ryan

images attached...thanks again

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.

ST_Superman

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

meadow_ryan

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

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.

meadow_ryan

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

ST_Superman

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:

meadow_ryan

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

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.

jamyoung

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