Scientific E Notation Conversion query

Hello All,

I am seeking few expert advise as I am stuck with my tool. My raw data has a specific column of references which includes Alpha Numeric as well as pure numeric references and these references are pretty huge like 22 characters. Currently my column is V WString so Alteryx is converting all Alpha numeric references perfectly but all my pure numeric references are getting converted to scientific E Notations. eg: 1.01000028133037E+18.

I want all these references to be readable as a whole and not scientific notation. I tried converting my column into a numeric field but that is affecting my Alpha Numeric references. Is there a way where alteryx will be able to read the column pure numeric values as is without converting them to scientific notation.

Any help or suggestion is welcome.

Thanks

Data Investigation

Accepted answers

atcodedog05

Hi @skotian1289

My before suggestion approach did work complete accurately. I think issue is caused because of huge numbers. Here is a hack. I am using string functions to get the number.

Input:

Output:

Workflow:

Hope this helps 🙂 Feel to ask if you have any questions

If this post helps you please mark it as solution. And give a like if you dont mind 😀👍

expn.yxmd

atcodedog05

Hi @skotian1289

You can say whats happening is a rounding off error. Its rounding off after a n large number of decimals.

It could be fixed in excel itself if possible. Excel is also very inflexible sometimes during conversion.

Just a thought input 2 file ABC (is in 1 column) and 202008200201471000000 (is in column 2) why dont you write a formula there itslef to create the key and then load it to Alteryx for rest.

Or you can use Find & Replace tool. Trim all the zeros of column 2 and create the key which will end up like

ABC202008200201471. And Find Key2 in Key1 like below

There might be a slight chance of mismatch and multiple match. Hoping it doesnt happen.

Hope this helps 🙂

key.yxmd

All comments

atcodedog05

Hi @skotian1289

1.01000028133037E+18 is basically 1.01000028133037 * 10 power 18.

So you split and covert it to value. By doing something like below.

On regex i am splitting

On formula i am calculating

[Base]*POW(10, [Power])

skotian1289

@atcodedog05 -Thanks a lot for ur response, Would you be able attach your workflow just want to refer it.

Also if I split the values and apply the formula, will it apply to all the values in columns or will it only apply to values which are converted to scientific notations. Because I don't want to touch the alpha numeric values as they are perfect as of now.

Thanks

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703