cancel
Showing results for
Did you mean:

# Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
###### #SANTALYTICS

The highly anticipated Alteryx Community tradition is back! We hope you'll join us!

## trim on variable length

Atom

I have data that represents the hours in a day, however I need to trim 2 zeros off the data.  Hour 1 currently looks like 100, hour 10 looks like 1000, and hour 24 looks like 2400.  How can I trim the 2 zeros off the end?  I tried

tonumber(left(tostring([HOUR]),2))

But that results with hour 1 being 10 and instead of 1.

I tried

trimright([HOUR],"0")

But that resulted with hour 10 being 1 instead of 10.

Quasar

Hello @marlylove

You can try this:

1. Convert your hours' column to V_WString

2. Use Formula Tool

3. Write the formula: TrimRight([Field1],'0')

4. Covert it back to Integer/Decimal as per your requirement.

Hope this helps,

Thanks,

Yug

Asteroid

I did it a bit longer of a way. I converted to a string, calculated and turned to a number and dropped the field I no longer needed.

Highlighted
Atom

This took off all zeros so hour 20 shows up as hour 2.

Nebula

Since your data is already a numeric datatype you can use this

[Hour]/100

this will only work if your data is in complete hours, 1300 as opposed to 1330.  If you have minute data as well, i.e. your data doesn't end in 00, change it to

floor([Hour]/100)

Dan

Quasar

@marlylove you can put a "IF condition" for the TRIM condition so that it applies only when the length is greater than 3 for example or 4 depending upon your use case. Please let me know if this works.

Thanks,

Yug

Quasar

@danilang  Dividing by 100 will convert some of the hours into decimals.

Nebula

Hi @ydmuley

That's what the

floor([Hour]/100)

is for.

Dan

Labels