Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Input Data Errors - problem with excel formula

newbiee
6 - Meteoroid

Hi all,

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...

 

KR

Lukas

 

8 REPLIES 8
BenMoss
ACE Emeritus
ACE Emeritus

Hey, welcome to the community!

 

Is it possible to post the excel file that is causing the issue? If not could you recreate the issue with a censored excel file?

 

Ben

NicoleJohnson
ACE Emeritus
ACE Emeritus

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! :)

 

NJ

newbiee
6 - Meteoroid

Hi Nicole, yep that works. Is there any option to not change that in the data file but in alteryx after upload?

 

Thanks again!

NicoleJohnson
ACE Emeritus
ACE Emeritus

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.


Super weird.

 

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. :)

 

Happy Friday!


NJ

newbiee
6 - Meteoroid

Hi Nicole, yes its doubly super weird because I don't have Microsoft Excel Legacy in my alteryx version 11.0. What version do you use?

 

I was aware that if I replace the formula inside the cell by string it works but I thought I did smth wrong and I wanted to find more automatic solution:)

 

Have a nice day!

 Input_list.png

NicoleJohnson
ACE Emeritus
ACE Emeritus

Strange indeed!! I'm on version 11.0.5.26351, 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!

kk0117
5 - Atom

I am using a directory tool to pull files and am getting the error "00:00:00" . I followed your suggestions and tried to change to format to "General" & Text. However it does not work.

 

I also have a batch workflow which picks up the columns currently however gives me an error in my main workflow

 

 

WJGonzaga
5 - Atom

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. 

3. Saved and closed file. 

4. Ran Alteryx workflow.

Labels