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.
