add leading zero

Reply
Yellow Belt

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

AS Black Belt
Black Belt

Re: add leading zero

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"
Yellow Belt

Re: add leading zero

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 Black Belt
Black Belt

Re: add leading zero

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"
Yellow Belt

Re: add leading zero

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.

Black Belt

Re: add leading zero

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 Black Belt
Black Belt

Re: add leading zero

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"
Yellow Belt

Re: add leading zero

In here? What is LPAD()

Yellow Belt

Re: add leading zero

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

Black Belt

Re: add leading zero

12.png

 


______________________________________________________________________________________________
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
______________________________________________________________________________________________
Announcements
Looking for the latest Community solutions? Please visit our accepted solutions board here!