topic Re: Scientific E Notation Conversion query in Alteryx Designer Desktop Discussions

Scientific E Notation Conversion query

skotian1289 — Tue, 01 Dec 2020 13:49:21 GMT

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

Re: Scientific E Notation Conversion query

atcodedog05 — Tue, 01 Dec 2020 13:56:48 GMT

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])

Re: Scientific E Notation Conversion query

skotian1289 — Tue, 01 Dec 2020 14:10:10 GMT

@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

Re: Scientific E Notation Conversion query

atcodedog05 — Tue, 01 Dec 2020 14:25:16 GMT

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 😀👍

Re: Scientific E Notation Conversion query

skotian1289 — Wed, 02 Dec 2020 06:16:16 GMT

@atcodedog05 Thanks a lot for your assistance.

I want to share a sample with you just to clarify if this is how it works. I am reconciling 2 files, in one of the input files the value is ABC202008200201471060826 and othe input file has the values as split ABC (in 1 column) and 202008200201471000000 (In column 2) which I converted from scientific notation to standard . so when I concatenate the values from 2nd input file I get value as ABC202008200201471000000 which will never match with file 1.

My question here is zeroes which are added post conversion in 2nd file (202008200201471000000) is it bcz the original input file doesn't have the full value or is it that our conversion formula is incorrect. Because as I stated the actual reference value should be 202008200201471060826 .

Just want to understand this logic of scientific notation, ur solution is perfect otherwise.

Thanks

Re: Scientific E Notation Conversion query

atcodedog05 — Wed, 02 Dec 2020 06:30:23 GMT

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 🙂

Re: Scientific E Notation Conversion query

atcodedog05 — Wed, 02 Dec 2020 07:53:59 GMT

Happy to help 🙂 @skotian1289

Cheers and Happy Analyzing 😀

Feel free to reach out if you face any issues 🙂