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

alteryx server Discussions

Find answers, ask questions, and share expertise about Alteryx Server.
SOLVED

Excel Formatting Breaking Alteryx Workflow

Highlighted
Meteoroid

Hello fellow Alteryx users,

 

Our internal data warehouse (DW) exports business reports into a CSV file before sending it Alteryx for processing. The CSV file contains 500,000 plus rows of data. The file will export the product number in the format shown below (see table). A manual intervention is performed to convert the contents of the product number in the table below to a 'Number', using the following formula - 

 

=IF(LEN(INDIRECT(ADDRESS(ROW(),COLUMN()-1)))>7,LEFT(TEXT(INDIRECT(ADDRESS(ROW(),COLUMN()-1)),0),4)&"E"&IF(LEN(TEXT(INDIRECT(ADDRESS(ROW(),COLUMN()-1)),0))-4<=9,0,"")&(LEN(TEXT(INDIRECT(ADDRESS(ROW(),COLUMN()-1)),"#"))-4),IF(ISNUMBER(INDIRECT(ADDRESS(ROW(),COLUMN()-1))),LEFT(TEXT(INDIRECT(ADDRESS(ROW(),COLUMN()-1)),0),4)&"E"&IF(LEN(TEXT(INDIRECT(ADDRESS(ROW(),COLUMN()-1)),0))-4<=9,0,"")&(LEN(TEXT(INDIRECT(ADDRESS(ROW(),COLUMN()-1)),"#"))-4),INDIRECT(ADDRESS(ROW(),COLUMN()-1))))

 

Product No

4.00E+03

7.01E+03

7.08E+03

7.10E+03

7.14E+03

7.18E+03

7.20E+03

 

On saving and closing the CSV file, the changes made are not retained and therefore the contents of the CSV file returns to its original state before the manual intervention. With that said, I would be grateful for some advice on how I can complete the manual excel intervention in alteryx.  Any suggestions would be greatly appreciated. Thanks in Advance. 

Quasar

Hit @Data_User88

 

Is there data lost in the conversion to scientific notation?  i.e. are your product numbers more than 3 digits?  If not you can use the following in a formula tool

formula.png

 

Take the first 4 characters from the input string, convert to a number and multiply by 100

 

Here's a workflow that shows it in use.  It read from a csv called Product.csv in the same directory as the workflow

 

Solution.png

 

 

Dan

 

Meteoroid

Many thanks @Danilang for taking the time to reply to my post. Yes, the data is lost as a result of scientific notation. Our product numbers are seven digits in length and typically follow the following format N,N,N,A,,N,N. (N= Numeric & A = Letters). Nearly all of our letters begin with a 7 but that is not strictly true as presented in the example above . 

Quasar

Hi @Data_User88

 

If the data is lost in the conversion, how do map what you get to the correct product IDS?  Can you post some sample data, before and after.   Just a few conversions is enough.

 

Dan

Meteoroid

Thanks @Danilang for your help. Its greatly appreciated.