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
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
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"1 -
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
0 -
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"0 -
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.
0 -
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.” -Superman2 -
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"0 -
-
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
0 -
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman3 -
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"0 -
So I've done the first part but where do I add the second string operation....see attachment
0 -
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"0 -
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.
0 -
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.” -Superman0 -
-
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.” -Superman0 -
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.” -Superman0 -
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
0 -
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.” -Superman0 -
at first glance it looks like there are just 3 rows which have bank_id's with less than 8 digits
0 -
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.” -Superman0 -
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.
0 -
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.
-----------------
Chris0 -
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.
0
Categories
- All Categories
- 1.7K Product Ideas
- 1.7K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 295 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.8K Transform
- 97 SQL DataFlows
- 608 Datasets
- 2.1K Magic ETL
- 3.8K Visualize
- 2.4K Charting
- 710 Beast Mode
- 50 App Studio
- 39 Variables
- 668 Automate
- 170 Apps
- 446 APIs & Domo Developer
- 45 Workflows
- 7 DomoAI
- 33 Predict
- 13 Jupyter Workspaces
- 20 R & Python Tiles
- 391 Distribute
- 111 Domo Everywhere
- 274 Scheduled Reports
- 6 Software Integrations
- 116 Manage
- 113 Governance & Security
- Domo Community Gallery
- 31 Product Releases
- 9 Domo University
- 5.3K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 103 Community Announcements
- 4.8K Archive