This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
I am new in alteryx community (my first subject) and I have an issue with a data input. Database table have formulas in a rows(excel file). After uploading it to the alteryx i get incorrect result of the formulas. In excel file the result of the formula '=G45' gives me string 'Record' in alteryx it shows '00:00:00'. I tried to look for the answer but I haven't found anything. Thank you in advance for help or any suggestions...
Actually, I ran into a situation like this recently with an odd field showing up like a date, pretty sure I know what's causing it - the field that is in your Excel file is probably formatted as a Time format... so even though it looks right in Excel because Excel sees that it's a word not a time, when Alteryx reads the file, it tries to make the data match the formats that are designated in the Excel file, so it's converting it to Time (and a word converted to time will show up as 00:00:00...)
So if you can change the format of that field in your original Excel file to General or Text or something like that, you should be good to go! :)
Found a way!! Couldn't possibly tell you why this works, but if you change the input file type from Microsoft Excel to Microsoft Excel Legacy, it will work. Also, I noticed that the field format in Excel doesn't matter if you have the word typed into the cell but formatted as Time... it only inputs it as Time format in Alteryx if it gets the word in the Time cell via a formula. So since your cell actually contained "=G45" not the word "Record", it was showing up as Time... but if you were to type Record into that cell, instead of the formula, it would show up as Record.
Anyway, if you switch the Input Tool to bring in the file as Microsoft Excel Legacy instead of just Microsoft Excel, that might fix it for you! Someone with far more technical expertise than I would need to tell you why that works haha. :)
Strange indeed!! I'm on version 18.104.22.168351, but I feel like the Excel Legacy option was available even before, maybe even in version 10?? This sounds like a technical question... :) You might post a different thread in the Community asking for some clarification on that!
So what I found in my case was, because I formulas, for example vlookups, that are embedded in my excel input file/sheet, allowed the first line to produce a #N/A for a non match. What I found was when I replaced all #N/A and NULL values with blanks, Alteryx digested those fields correctly.
Therefore, my solution was a few more data prep steps in excel before I sourced from it in Alteryx.
1. I copied and pasted all values as Values. This eliminated embedded formulas.
2. Then I found and replaced all #N/A and NULL values.