community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Upgrade Alteryx Designer in 10 Steps

Debating whether or not to upgrade to the latest version of Alteryx Designer?

LEARN MORE

trim on variable length

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.

 

 

 

Please help!

Bolide
Bolide

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.

 

trimRight.PNG

 

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

Aurora

Hi @marlylove 

 

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

 

Bolide
Bolide

@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

Bolide
Bolide

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

Aurora

Hi @ydmuley 

 

That's what the 

 

floor([Hour]/100)

 

is for.

 

Dan

Labels