How to clean simple hourly data

[lukemanderson12]

Hey! 

Thanks in advance for any help I receive!

I have what I believe is a simple request. Would really appreciate some guidance.

I have an excel of days like this photo.

Basically, I want it to clean the data for incorrect submissions like the one on day 3, 1..0 is an incorrect submission and I want Alteryx to find it and replace it with 1.0

Any ideas?

[lukemanderson12]

Tried to use imputation but I think there's too many possibilities and doing all of them would be inefficient.

[lukemanderson12]

I think I fixed it using select and changing the data to double. 

Any other alternatives or will this generally fix it?

[lukemanderson12]

This solution didn't work, I realized it removes the decimal completely.

I.e "8..5" turns into 8 not 8.5 which is what I need

[RolandSchubert]

Hi @lukemanderson12 ,

is it only ".." or are there other errors? You can use a Multi-Field Formula tool to replace ".." by "." (and of course other characters).

I've attached a sample workflow.

Let me know if it works for you.

Best,

Roland

[lukemanderson12]

Thank you!
 There can be other issues as well, maybe like a "9." that needs to be reformatted to a 9. and so on. I understand that i'd have to be somewhat specific regarding the issues I'd like to fix. 

Thanks in advance

[lukemanderson12]

Hi Roland, 

Maybe something regarding this idea might help

If (ENDWITH(string, '.')) then RIGHTTRIM(string,1)?

I'm not sure why it's not formatting / working?

[lukemanderson12]

Picture for guidance

Parse error.

[RolandSchubert · Accepted solution]

Hi @lukemanderson12 ,

I've added a second Multi-Field Formula tool with the formula to cut off the last character, if it's a '.'. It would be possible to do it within one formula, but I think my approach is better readable.

Best,

Roland

[lukemanderson12]

Wow! Thanks so much, this makes me so excited about the capabilities of Alteryx thanks so much.