Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
ALTER.NEXT:

Join us on Dec 2 for a half-day virtual analytics + data science event!
US & CA customers only

SAVE YOUR SPOT
It's the most wonderful time of the year - Santalytics 2020 is here! This year, Santa's workshop needs the help of the Alteryx Community to help get back on track, so head over to the Group Hub for all the info to get started!

How to keep the leading zeros in the output

Highlighted
8 - Asteroid

In the input file we have leading zero in one of the column but the same is missing in the output file. we are not doing any manipulation with this field. The output file is generated in csv format. Please let me know what change need to be done to keep the leading zero in the output file.

Highlighted
Alteryx Partner

Hi @vsalver,

 

What type of field is the field you are interested in?

 

Best,
Yalmar

Highlighted
17 - Castor
17 - Castor

Hi @vsalver 

 

This happens because Alteryx interprets the input 01234 as a number and removes the leading 0. Before your output, you'll need to convert the field to a string and use a formula tool to padleft([fieldname],"0", x) where x is the required length  

 

Dan

Highlighted
8 - Asteroid

padleft([fieldname],"0", x) where x is the required length  

 

There is no standard length and also not every record has the leading zero. I should have the leading zero as per the input file.

Highlighted
17 - Castor
17 - Castor

What type of file is the input?  Can you post a sample with a few values

 

Dan 

Highlighted
Alteryx Partner

Can you determine the length of a field using Length(tostring([fieldname])) and use this as x in your formula padleft(tostring([fieldname]),"0", x)

Highlighted
8 - Asteroid

@Yalmar - Currently we added it as V_String. Our data is somewhat like below and the output file should be similar as well.

 

00AB

0000123456

0A12548

6890

8HRI900

Highlighted
Alteryx Partner

@vsalver, you shouldn't lose any leading 0s as long as you type still is a string (or V_String).

Do you alter the data in your flow?

 

Are you able to share your flow with us?

Highlighted
17 - Castor
17 - Castor

I think I see where your problem lies.  If you take your input data and output to a csv using a workflow such as this

 

WF.png

 

and open the csv in notepad you get this with the leading zeros.  Notepad performs no interpretation of the value type

 

Notepad.png

 

However, if you open the csv in Excel, you get this.

 

Excel.png

 

Excel has interpreted the values without letters as number and removed the leading zeros.  This is an Excel issue and not an Alteryx one.  

 

If you open Excel and import the data from the CSV file Data->From Text/CSV then you get this where excel does not interpret the data type

 

 

Excel Import.png

 

Dan

 

 

 

 

Highlighted
6 - Meteoroid

I can verify that all the suggestions provided do work. As mentioned by @danilang , opening the CSV in Excel removes the leading 0's while opening the CSV in a notepad editor does show the zeros. Thank you everyone for your solutions!

Labels