output on Server differs from output when run locally: custom macro to blame(?)

[Greenlama254]

Hi all

I have an issue I was not yet familiar with. Perhaps because I am quite new to building my own batch macro. 

Because the contents of the daily file I want to process differs slightly (only the format of a specific column differs) using a simple "Input Data" did not work. I therefore used a "Directory" tool and a custom made batch macro. Running this flow locally worked like a charm. All input files were processed and the output was as expected, so I was really happy to build this (relatively simple) batch macro.

However, when I upload the flow to the Alteryx server the output differs, even when I download the uploaded flow and run it locally again. Not only the data from the first input file (the one mentioned in the macro) is used but all lines are duplicated. I expect that my first custom made macro is to blame but still, I would not expect it would result in different output either run locally or on server.

Please see attached screenshots of the macro.

Can anyone see where the problem lies? (cannot share all information due to company sensitive data)

Why would the output differ between running the flow locally vs from the server?

Thanks in advance

Regards,
Roger

[OllieClarke · Accepted solution]

@Greenlama254 in your second image, you can see that you're replacing a specific string, but that string isn't the filepath in the data input tool.

When uploading the macro, I imagine it was packaged and the filepath to the file was made relative. So if you change the specific string (the highlighted bit at the bottom in your 2nd image) to "..\Input[...]Jan 2023.xls" (filling in the [...]) then that should fix that issue. There could be other issues to do with access to the directory, but this is why the data is being duplicated at the moment, because it's reading in the same file for every row that enters the control parameter.

Hope that helps,

Ollie

[Greenlama254]

Thanks Ollie, I will give that a try later today. 
Thanks again
Roger

[Greenlama254]

@OllieClarke turns out the problem seems somewhat more complicated.

Before uploading to the Alteryx server the macro looks fine and the lines in the Input Dat (10) tool is identical to the line in the Action (12) tool as you suggested. (see screenshot Macro 4)
Because of your answer I came to realize that I myself never changed that but it seems that the upload to the server causes that. Because after the upload the Input Data looks like I showed in previous screenshot (Macro 5 Input Data changed):

 ..\_externals\1\Settled Cash Statement - separatie - Daily overview_04 Jan 2023.xls

I don't know if this has anything to do with the actual issue but I also ran into the following problem when uploading to the Alteryx server

Apart from the daily files I want to batch upload with my custom macro there is a file with golden copy data I need to enrich the data with. 

However, that golden copy data is in XLS format and for some reason I cannot upload the entire flow to the Alteryx gallery with a XLS file included (see attached).

Our support team therefore suggested to change that File Format to *.xlsx.

That seemed to work, because the flow still worked fine locally and the flow would upload to the Alteryx server.

Again, I do not know if this last part has anything to do with the batch macro not functioning properly, but I need to change the format of that golden copy file first to be able to check whether the flow runs properly from the server, which so far, it does not. 

Hope you or anybody else can help. Appreciate it

Regards

Roger

[OllieClarke]

@Greenlama254 
I would also agree that .xlsx is a better format for the files than .xls which hasn't been used really since 2003. However, i'd suggest saving as xlsx from your golden copy in excel and then read that file in, rather than pointing to an .xls file and changing the file format of the input data tool. 
Then you won't have the issue of not having the driver needed to read .xls files.

However, this is a separate issue to what we were talking about before. Your batch macro is set up to read in the "Settled Cash Statement - Separa" sheet from whichever excel file is fed into the control parameter, but when you publish the workflow and package the macro, alteryx saves a snapshot of the template file with the macro and uses that as the basis. What that means is the filepath in the input data tool within the macro changes to a relative filepath (..\Input[...]Jan 2023.xls) and because your action tool is replacing a specific string, then it can't find the original filepath to replace.

You've got 2 options here, either update the macro once you've opened the workflow from gallery and replace the specific string section of the action tool to reflect the new filepath.
Or alternatively, instead of replacing a specific string, you could untick that option and just replace the whole filepath. Then in a formula tool outside of the macro, add '|||Settled Cash Statement - separa$' to the end of your filepaths (which would then point to the correct sheet). This way you won't need to worry about the filepath changing when publishing.

[Greenlama254]

First of all: thanks for putting all your your time effort and patience into replying to my questions. Although I am very enthusiastic about Alteryx, my experience is not that great yet. I am quite new to building workflow alltogether and sometimes it is really hard for me to understand the suggestions.

I went and tried your first of 2 suggested solutions, because, well.. it was the first and seemed like the easiest one.

(just to make things clear, when you say "either update the macro once you've opened the workflow from gallery".. with that you mean download the workflow again after uploading it to the Gallery, right? Because I do not see how else I can edit the workflow after upload to the Gallery.)

Because I did just that and I simply replaced the line at the bottom of the action tool to match the line in in the Data Input tool, both in the custom macro. (see screenshots)

However, that got me the following error. The error refers to a d:\  drive path which I do not even recognize. perhaps that is the Gallery path, in that case it kind of makes sense. But not why the path would be wrong, not to me anyway.. 

regards

Roger

[Greenlama254]

I think it is like attached what you initially meant isn't it @OllieClarke ?

I thought I tried that before, but anyway, it seems to be working now. 

I am not fully convinced but I will keep running a few more examples. 

It kinda makes sense that when I change the paths in the entire flow to UNC that I do the same for all the paths in the macro (that is may layman explanation for this solution, please correct me if I'm wrong here)

again: thank you so much! 

[OllieClarke]

Hi @Greenlama254 

Yup that will work too - as long as you don't package the .xls when publishing to gallery (this would change the filepath as had happened originally) 
There's more information about assets and packaging them here: https://knowledge.alteryx.com/index/s/article/Assets-and-Packaging-Workflows-1583460636757 but basically when you tick an asset to be packaged, alteryx saves a snapshot of that asset and zips it with the workflow/app either in the same folder, or in an _externals folder. This can change filepaths, which normally doesn't matter except for the fact you were looking for a specific string which no longer existed.

Glad you've got it working now though with the UNC path.