Alteryx Reading Columns from Input but NOT the data that is in the columns

I have two legs of a workflow.

The first leg takes an input, manipulates it and outputs the data ("Altreyx Output"), which has three columns (Month & Year, State, Amount). This data is output to an existing workbook that has other tabs that summarize this data by period. For example, a summary tab and 4 tabs that detail amounts by state and month for each quarter (e.g., Q1, Q2, Q3 & Q4). These sheets are formula-driven and populate with data from my 'Alteryx Output' tab.

The second leg uses the existing workbook I noted above as input. I then take the data by period and use it for other downstream reporting which isn't pertinent to my problem.

The Problem: I was able to output to my file and subsequently use it as an input without issue, as they are in separate containers. However, even though the data is correctly populating to my desired files, when I use it as an input, it does not contain the data (amounts). It pulls in the schema as expected with all of the headers and rows, just no underlying data. It should also be noted that I have been running this for days without issue, all of a sudden this is happening.

I copied the sheets into a new workbook, re-ran and it worked. But it only works for the initial run after deleting or putting in a new file, then it reverts back to not showing the data.

Any thoughts on why this is happening?

Workflow

Datasets

Reporting

Output

Data Investigation

Accepted answers

All comments

apathetichell

what version of Alteryx are you running and if you declare a path to ouptut your first file can you share the formula tool where you generate that path? does it contain a range?

Cbennesn

I am running Version: 2022.1.1.25127.

For the first leg, my output is actually output twice. Once as a true output to a folder location, and another to an "Input" folder. They are the exact same, this way I am not using an output as an input, if that makes any sense.

I do not declare or define a file path using a formula tool, it is just the path where I saved my xlsx file down to. It does not specify a range

AGilbert

This sounds suspiciously like issues that crop up with the Output Data - Output Options configuration (attached file for reference).

Agree with @apathetichell, any workflow details you can share is helpful.

Screenshot 2024-03-13 120822.png

Cbennesn

I have included a few images. Item A is the true output, Item B is the output that is used for the second leg of the workflow as an input. As I mentioned before, it had been working fine until today.

For example, if I try to run this workflow for March 2024, the excel file will correctly populate with the data, but when pulling it in via Input B on the second leg, it will not pull in the data, even though it is present in the excel file. My "Alteryx Output" tab has headers in the file so I use the "Append to existing sheet" config.

Not sure if it matters but both "legs" of the workflow are in separate containers. I understand Alteryx reads top down, left to right, so it shouldn't be an issue with pulling the input before it is written but maybe I am wrong?

Thanks for the detail!

Is Image 3 the output of the B input file anchor in Image 2? If leg one ends with data appended to an existing file, I'd expect your March numbers to be below the 0's in that table.

A separate thought. If the Image 3 table is constructed within one of the legs (rather than read from a file) do you have a Select tool with 'unknown' deselected which would capture data from a new monthly column?

apathetichell

can you confirm that AMP is off?

Cbennesn

AGilbert - Yes, image 3 is the output of the B input file and AMP is off. I have attached the file for reference so you can better understand the process. The 'Alteryx Results' tab is my output from the first leg of the workflow. and the data is appended every time a user runs it for a new month.

I then use this same file as my input in the second leg, users can select any of the tabs as input, as they are all updated via formula from the 'Alteryx Results' sheet.

If I open this file after running the workflow, the data is updated as intended. When using it as an input, the numbers are not present. So using the example file attached, if I ran it for April 2024, the amounts are populated but would show as zero when used as an input. I tried using control containers to solve this in the event it was an ordering issue but that did not solve anything.

Test Template 2024.03.14.xlsx

AGilbert

Thanks @Cbennesn for the helpful explanation. By updating the 'Apr 2024' values to 'Mar 2024' the input tool works as expected. At least that rules out silly things like excel format/formulas. I've attached the workflow package to document for others.

I did notice the total column in 'Image 3 Alteryx.PNG' is the correct row subtotal for two months. I believe that if the input table were mysteriously dropping the March column the subtotal would be unchanged from the underlying file. In other words, the rows would not reconcile across. Could it be that an outdated file is being selected from the analytic app portion of your workflow? You could troubleshoot this by importing the file with the full path or file name appended.

Thanks for letting me throw some ideas at the problem. Good luck!

Alex

test.yxzp

Cbennesn

Thanks for the help. I am still unable to get it to work and not sure what the issue could be at this point. As I mentioned, when I change the month, the data writes to the output as intended with no issue. When using that file as an output, where I would expect the most updated table to be used, it is simply not pulling in the values.

I created new files, changed locations, did it on a shared drive and my local drive and nothing impacts it. Even using Control Containers had no effect. Is there any reason a formula in my sheet could cause this issue? Also, on my 'Alteryx Results' sheet, the data is not starting on row 2 as I would expect, it's starting on a random row further down in the sheet. Not sure if that would impact it as the VLOOKUP I am using is for the full range.

AGilbert

The most effective way to get help on this message board is to include your workflow as an attachment to give other users the ability to replicate the issues and see the dependencies of a complex problem.

You can attach a .yxzp (zip package to include the underlying excel files) by using Export Workflow under the Options menu in Designer. If you do this, please take care to mask any proprietary data. Alternatively, you can simply share the .yxmd file as a stand-alone.

The community post below seems similar and suggests a caching issue with Alteryx itself and suggests contacting Support. If you've not searched the community posts yet I'd encourage you to do so. It often times leads to a lightbulb moment.

Solved: Input data issue - Alteryx Community

Regarding the excel format, it seems odd that the data is being written to the 'Alteryx Results' sheet at a random location. This suggests a review of your Leg 1 output. From the limited review of the excel sheet your sumif formulas were set to an infinite column range; so, they should still function regardless of top padding.

Have you tried temporarily replacing the Interface connected Input Tool (with the lightning icon on top) with a fresh Input Tool directly connected to the correct file? This could help isolate a failure. To do so, you could simply drag a new icon to the canvas and divert the connection. This keeps you from deleting existing tools so you can toggle between sources for testing.

Cbennesn

Ok, I am still pulling my hair out over this one, as I cannot find a solution for it. I have attached a dummy workflow and more images. The first input file here has three columns (month & year, State, and Sum of Tax/Credit Amount). These three columns push an output to an excel file that I previously attached and results are shown on an "Alteryx Results" sheet. Those data points are pulled into other sheets via VLOOKUP.

Then that file is used as an input, this time using one of the sheets that has the VLOOPUP formulas. The workflow is populated with March data. If you run it, you will see the second input shows nothing for the month, but if you actually open the file, you can see the data is in fact populated. You can change the month in the formula tool to replicate the results.

I've tried this with and without control containers, with and without AMP, on and off a shared drive vs local and nothing seems to change. I am beyond confused as to why the input is not reading the amounts.

Any and all help is greatly appreciated.

SamplePicture1Results.PNG

Sample Workflow.yxzp

SamplePicture2Results.PNG

AGilbert

I changed a few things and have the March values loading to leg 2. I'll try to be as succinct as possible here and attached the edited workflow for reference.

Screenshot 2024-03-20 103455.png

Excel format change. Q1 - Credit Summary E8:E22 to the 'accounting' cell format (by clicking the comma button).
1. It is possible for data types between Excel and Alteryx to be mismatched and, when this happens, you'll get unexpected results from Alteryx.
2. I believe this was the main issue.
Leg 1 Output tool. I used 'Overwrite Sheet or Range' instead of 'Append to Existing Sheet' because the latter causes an additional set of 58 records to be added to the table.
1. Please see the config of the output tool in the attached workflow for updates in lines 1, 3 and 5.
2. Your original file includes prior quarter-end values in the output. The range here should be adjusted to facilitate the output of that data as well (i.e. likely 58 *2).
3. I believe this was a secondary issue caused by iterative runs of the workflow. In production, when the workflow is only run once. The append output method may be perfectly fine.
Leg 2 is assigning the string datatype to all the columns and your downstream select tool (where the datatype is switched to double) is not converting correctly.
1. I added a formula tool with the tonumber() formula as an example to convert the string.

As an aside, please take these as suggestions and test for downstream impacts. Especially if this is an already established workflow.

Sample Workflow.yxmc

Test Template 2024.03.19- input.xlsx

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15878

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703