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!

Engine Works

Under the hood of Alteryx: tips, tricks and how-tos.
AdamR_AYX
Alteryx Alumni (Retired)
The latest release of the CReW macro pack is available for download now at
 
 
NOTE: You will require Alteryx 10.0 or later to use this macro pack.
 
For installation instructions see this page
 
NOTE: Check out this Knowledge Base article for Support policies and fixed/ known issues.


WHAT'S NEW?

 

New Macros

 

Wait A Second - This month I am very excited to be able to introduce another new contributor to the CReW macro pack: Daniel Brun.  Who as well as contributing this macro here has also released his own macro pack "The Inviso macro pack for Alteryx" available for download here.
 
The Wait A Second macro is a different spin on the throttle tool which allows a user to specify a wait time between records passing through the tool.
 
 

Blocking Test Tool - This macro came directly from a client request and is actually something which I have seen asked for many times before.  It is very useful when updating an external data source and you want to check some condition before you write *any* data out.  It checks all of the data against the given expression and only if all of the records pass that expression does it then pass its records downstream.  If any record fails the expression then an error message is thrown and no records are output.
 
 
The Test Macros
 
These are four new macros which were born out of a series of blog posts I wrote back in 2015 about trying test-driven development for Alteryx macros.  That experiment actually hit some stumbling blocks which I am yet to write up about, but the macros that were born out of it live on and are very useful for testing in a TDD style or otherwise.
 
I am releasing them in this release with a BETA flag as they are still very much a work in progress and I know I still have some work to do on them.  However, I believe they are in good enough condition to get some other people looking at them and feeding back any issues and improvements they might find.
 
They are under their own tool category "CReW Test" which you can hide for now if you want to wait for them to evolve some more.
 
They are:



Test Runner - This is the macro that runs all of your tests.  You generally have a workflow containing only this macro.  Running that workflow will run all of your tests.
 
 


Expect Equal - This macro takes two data streams (Expected and Actual) and throws a (hopefully useful) error message if they differ.
 
This is the macro where I know I have some work to do and where you as a community can help out.  I need any examples you can find where this tool isn't doing what you expect.  So data sets you think are equal, but it thinks are different and vice versa.

 
 


Expect Zero - This macro expects to receive zero records into it and if it doesn't, throws an error telling you.
  


Expect Error - This macro is for checking that workflows correctly error.  An essential part of testing.  If it is placed within a tool container then it expects a tool in that same tool container to throw the expected error message.  If the correct error message is not thrown then the Test Runner will mark that test as failed.
 
I intend a follow-up article on how to use the macros, but to get started quickly take a copy of the GettingStartedTemplate folder in TestMacros, rename it to something meaningful and run the contained RunTests.yxmd to run the sample tests.
 
You can also take a look at the CReW macro test suite which you can find in Samples\CReW Macros\Tests.  Again a work in progress, but the starting of what is to follow.
 
As always please let me know about any issues/feedback you have and hopefully see many of you at Inspire Europe in September!
 
(If you are not registered already, what are you waiting for? http://www.alteryx.com/inspire-europe-2016)
Adam Riley
-

Former account of @AdamR. Find me at https://community.alteryx.com/t5/user/viewprofilepage/user-id/120 and https://www.linkedin.com/in/adriley/

Former account of @AdamR. Find me at https://community.alteryx.com/t5/user/viewprofilepage/user-id/120 and https://www.linkedin.com/in/adriley/

Comments
ryeldell
5 - Atom

Do the CREW macros have to be installed on Alteryx Server in order for a workflow with the Runner macro to run on Server?

AdamR_AYX
Alteryx Alumni (Retired)

@ryeldell Yes they do

ben_broom
5 - Atom

Hello I installed the 2016 Q1 Release of the crew macro pack.   I am getting the following error messages when I try to run the runner macros. all the other macros seem to work.any ideas on what could be causing this error?

 

Error: List Runner (1): Tool #3: Iteration #1: Tool #5: Record #1: Tool #2: Tool #2: The external program "SupportingExes\AlteryxRunner.exe" returned an error code: -1073741512

Error: List Runner (1): Tool #3: The output connection "Logs" was not valid

AdamR_AYX
Alteryx Alumni (Retired)

@ben_broom Are you running 32 bit windows?

ben_broom
5 - Atom
No, I am running 64 bit windows
MikeN72
7 - Meteor

Hello

 

Great macro pack, thank you.

 

I wonder if you could help here, though. I am not sure if this is an issue with using the runner or events (or just runners related to events), but I thought I would check. I will try to explain this as clearly as possible :)

 

I have a "parent" workflow that contains a Runner (that runs "child A" workflow), and then from the success branch of this first Runner it will then run another workflow ("child B") using a Conditional Runner.

 

If there is an error in either child A or child B (or both) then I want the parent workflow to send an error message.

 

Now, I have a customer who hires a server where they only allow secured email sending  (using a secure smtp), so I cannot use an Alteryx email tool after the Runner tool and Conditional Runner tool fail outputs to send a message.

 

So, as I can't use the Email tool with the Runner & Conditional Runner, I have an After Run with Errors event that calls sendMail to send an error message.

But, this looks for an error in the parent workflow (that contains the runners), not the child A and child B workflows that the parent workflow Runner & Conditional Runners run.

After Run With Errors Event to sendMail if error(s) occurr in child A or child B workflow runs donw by parent workflow using a Runner & Conditional RunnerAfter Run With Errors Event to sendMail if error(s) occurr in child A or child B workflow runs donw by parent workflow using a Runner & Conditional Runner

I have done a fair bit of testing and I can see from making the child A workflow fail, the flow channels down the Fail output of the first Runner. Using a Log Parser I can see details of the fail in child A.

 

Is there a way to get this child A error to register as an error in the parent workflow (or escalate the error to an error in the parent worklfow) so it will send the After run with Errors event email?

 

P.s. I have tried using After Run with Error events directly in the child A and child B workflows but for some reason the Runner picks up the truncated messages from the child A run, interprets them as conversion errors, and makes the child A After run with errors event fire off an error message even though there was no error (not even a single warning) with the child A run itself.

Child A run through Runner. No errors but Runner truncates messages and shows them as conversion errorsChild A run through Runner. No errors but Runner truncates messages and shows them as conversion errors

 

 

I know from working with customer set-ups where one can use the email tool that there is no problem with using them on the Fail outputs of runner tools - they work great - but in this set-up I can't use email tools...

 

I guess if I could read the contents of the Log parser and if it contained the text "Error" then that might be a start, but then how to create an error in the parent workflow that would fire off the After run with Errors event?

parent log parser shows child A errorparent log parser shows child A error

It'd be great with any ideas

 

Thanks

 

 

 

 

AdamR_AYX
Alteryx Alumni (Retired)

You are very close :)

 

So after that log parser add a Message tool configured like so

 

MessageToolConfig.png

 

And I think you have exactly what you need.

 

I have half wondered in the past about adding a new macro to do this message "promoting"

 

I think that truncation message you were seeing is likely a bug in my runner macros...  I guess a field set too short.  Do you have the latest version?

MikeN72
7 - Meteor

@AdamR

 

Thanks for your help - the Runners are great. Like you wrote, a promote tool would be great but again, as you showed - using a message tool to create an error was a great idea. Works a treat!

 

The latest version of the macros is 2016 Q2?

 

 

AdamR_AYX
Alteryx Alumni (Retired)

Yep that's the latest.

 

I see the bug.  There's a 254 char limit on log messages.

 

I added an issue for it https://github.com/AdRiley/ChaosReignsWithin-Alteryx-Macros/issues/15

 

 

gregh
7 - Meteor

@AdamR

 

these macros are great - we use them widely.

Unfortunately, we've just upgraded to Alteryx v11 and the alteryxRunner.exe is giving an error:

 

Error - ToolId 3: Internal Error: Unknown InitVar "AdditionalAliasFile"

 

this seems to occur for sql in / out connections (the target module runs fine if run normally, but if run with the runner macros / AlteryxRunner.exe then it fails, with this error in the logs)

 

Any ideas / is this something you've seen?

AdamR_AYX
Alteryx Alumni (Retired)

I've not seen that one before no, but I will have a look into it.  Is tool 3 an input tool with a SQL connection?

gregh
7 - Meteor

yes, although it seems that all input and output tools are affected (to sql databases or excel files)

AdamR_AYX
Alteryx Alumni (Retired)

If anyone else has the same issue with the "AdditionalAliasFile" error message.  We fixed it by updating to the latest version of the CReW macros.

@AdamR I'm getting the same error @ben_broom had:

 

List Runner (3) Tool #3: Iteration #1: Tool #5: Record #1: Tool #2: Tool #2: The external program "SupportingExes\AlteryxRunner.exe" returned an error code: -1073741515

List Runner (3) Tool #3: The output connection "Logs" was not valid

 

 

 

Windows error gets thrown tooWindows error gets thrown too

This is happening on the runner and list runner macros, but only on the VM I'm using. They work fine on my desktop. Both setups are 64-bit with admin executable rights. Any idea what could be going on? Thanks!

 

 

SME_Steve
7 - Meteor

Hi Adam,

 

First up thanks for all your work on this macro pack. Your tools help us massively especially the runner!

 

That's the good news :)

 

The bad news is that we're experiencing some issues with the runner macro.

I've managed to track it down to special characters in the log file causing AlteryxRunner.exe to stop processing the logs. This results in an incomplete log file coming back into the macro which is missing the final count and timing record that it requires to resolve success or failure.

It took us an age to find the issue as in our case we were generating dynamic azure powershell scripts within a macro and instead of hyphens one of the scripts contained a couple of long dashes (em dash) which were almost indistinguishable within the formula editor.

To replicate just create a simple workflow that generates a single row and then use the message tool to write out a special UTF-8 character like emdash or " ™ " to the log. The workflow will run fine with no errors or warnings but when executed using the runner it gives the exe some trouble and the log will end at the special character.

 

Thanks

userfriendly1
6 - Meteoroid

@AdamR can you look into what is causing the problem @PoweredByWindows7 had?
 
 
 
 I ran into the same issue and it's preventing me from being able to use the conditional macro. 
AdamR_AYX
Alteryx Alumni (Retired)
AdamR_AYX
Alteryx Alumni (Retired)

@SME_Steve Thanks for reporting this and for the work in tracking down the issue.  Given the work you have done I'm hoping it will be an easy fix.

 

However I'm afraid I am currently travelling and it will be a couple of weeks till I am back at my machine to take a look at it.

 

 

I have created an issue for it https://github.com/AdRiley/ChaosReignsWithin-Alteryx-Macros/issues/20

 

and please bug me in a couple of weeks time if I haven't got to looking at it.

 

Thanks

Adam

rohanonline
10 - Fireball

Hi @AdamR_AYX

 

With reference to the bug identified with 254 char limit on Runner log messages, do you have any updates?

 

I am facing the following issue with my workflow and am assuming that'd be the reason. Just to highlight, the error only pops up when the workflow is running through the scheduler. Else it works fine. Would you have any thoughts on that?

Snapshot.jpg

 

Appreciate your efforts and support.

AdamR_AYX
Alteryx Alumni (Retired)

@rohanonline - Co-incidentally I just fixed that yesterday!  Look out for a new release with the fix coming soon.

rohanonline
10 - Fireball

Thanks @AdamR_AYX - really appreciate the quick response.

 

Look forward to the new release. Any thoughts on when would that be out?

justin_neisuler
5 - Atom

Hello sorry if this was answered above/elsewhere. I have a "managed desktop" in a big corporation, without admin rights. I have been able to install and run Alteryx successfully in the past.

 

I downloaded and installed the Crew Macros discussed in this thread, but when I try to use the Runner macro, I get this -

 

Error: Exec Macro (0): Tool #2: Failed to run external program "SupportingExes\AlteryxRunner.exe": Access is denied.

 

Wondering if anyone else has had this issue, and if so if there's a simple workaround.

 

Thank you!

GwenH
5 - Atom

I am getting truncation messages in the Runner macro when using full UNC paths to a shared drive. I do not get the messages in the Conditional Runner macros using exactly the same locations.

 

Runner_message.JPG

 

 

My temporary solution/workaround was to create a dummy workflow containing only a comment ("Start Workflow!") which is run by the Runner macro and use the Conditional macros to run my real workflows.

 

 

 

Antai
5 - Atom

Hi Adam,

 

I wanted to reach out because we found the Crew Pack and with great delight installed it (currently on 11.5). We work in a multi-environment/application space and looked to the runner(list runner) to help us run a set of queries each morning which build up a dataset which our online reporting tool points to

This would be a savior but every time I use the workflow which uses the list runner to execute the queries which I have broken into 2-4 queries per runner workflow, it eventually crashes or fails about 3 or 4 workflows down. it seems like as it progresses through it continues to take up resource and eventually tanks

This pattern doesn't seem akin to Crew alone but overall the worklfows seem to tank on chunky tasks and hence why we went on to the list runner where we tried to break tasks up to as many little workflows and then execute them one at a time with the help of the runner.

 

We don't seem to have a sense of what the limits are of what Alteryx will happily execute without qualms at least so we can design tasks around this understanding or simply know that alteryx will not be happy with for eg. 2 million rows x 15-20 fields being manipulated with from multiple tables or certain level of complexity in your query. Our use case here is simply drop existing tables and write new ones using a create on a select query which could be of sizable length (as the query cross-checks conditions in other tables/derived tables or using a case and creates flags against each record which typically could be half a million to 3 million records) 

In the absence of alteryx, I simply go to oracle developer and drop the existing table and recreate using the create table as select.... each morning I need to refresh the data

asteryx
8 - Asteroid

Hello @AdamR_AYX,

I tried to install your macros, but after successful install, Alteryx will no longer start up.

I am running 11.7 x64. I downloaded your macro pack from http://downloads.chaosreignswithin.com/Macros.zip today.

I followed the install instructions - putting the zip in a new folder totally separate from the Alteryx folder.

After install is done, I open Alteryx. I get the initial "splash" box and the designer starts to appear behind it, but then it all freezes.

 

We are running Alteryx on Windows server 2008 R2. Never had any problems prior.

I have to kill Alteryx from the task manager.

Uninstalling the mod pack also seems to be successful, but Alteryx sitll will not start up.

 

Please help!!

 

 

AdamR_AYX
Alteryx Alumni (Retired)

Hi @asteryx

 

That sounds like a strange one!  I think first step to make sure they are completely uninstalled:

 

  • Delete the folder they are in (if you haven't already)
  • Browse to C:\ProgramData\Alteryx\DataProducts\AddOnData\Macros and make sure CReW.ini and CReWTest.ini are deleted.

After that you should be back to how you were pre installing the CReW macros.

 

Let me know if that solves your start-up issue.

 

Thanks

Adam

asteryx
8 - Asteroid

Thanks for following up.

The crisis is averted for now. I discovered later that there was a second session open on the server in which Alteryx was still open. I didn't realize it was running when I was installing/uninstalling. Do you think that that may have prevented Alteryx from restarting in the initial server session following the CReW install?

DAM
7 - Meteor

Hi Adam,

 

Great macros! I used the "CReW_AddTotalRowColumns" and published the workflow on the alteryx server. I have a little issue when the data is updated, the new columns (fileds) are not selected in the fields (for example new weeks). How can I make to take the new columns as well?

 

Thanks in advance for your help,

Marius

RicardoCaro
7 - Meteor

Hello @AdamR_AYX
Since the release of 2018.2 we are now able to create 'Workflow Groups' (one to many workflows in a single file).

Instantly, I thought of the ability to run a workflow group in the Runner/Conditional Runner tools.

 

What are your thoughts about this? 

Patrik_Khalaf
5 - Atom

Hello!

 

I am trying to install this addon on version 2018.3 but it doesnt work, i am following the instructions but i am geting an error while running install.

 

Best regards, Patrik.

rahuls
9 - Comet

Hi @SME_Steve @AdamR_AYX  : Wanted to check if  the issue regarding the alteryxrunner.exe stopping reading the log at special characters was fixed already? It seems I am getting that in a couple of workflows of mine.

ruxandra_valcu
5 - Atom

I am having problems using the Runner or Conditional Runner macros on flows containing fuzzy matching. I get [Null] values for Errors, FieldConversionErrors and Warnings, and 0 execution time in the runner output, although the actual runtime is clearly above 0s, and the messages before the fuzzy matching tool get written to the log.

 

Alteryx version is 2019.1.4.57073

LincolnMike
8 - Asteroid

i downloaded the 2018.3.7 version of Alteryx Designer x64 today, upgrade from V 11.7.

My Crew macro disappeared from the gallery, so I tried 3 different times to run install.yxwz to get it added and it does not appear...restarting Alteryx, and restarting laptop, and neither will display CREW.  I use the Add Totals tool in 75% of my workflows.  Any ideas?

Zooooey
5 - Atom

Just started getting my hand on Alteryx, I have to say that I like your Wyldstyle icon!  

naterusso
6 - Meteoroid

Hello, 

 

I have a question about the macros.  For some reason, they keep disappearing from the Alteryx toolbar every other day or sometimes every day and I have to keep reinstalling them.  Has anyone else had this problem? Do you know what's causing it and how I can fix it? I thought it was the anti malware program that my company uses, but they added the filepath as an exclusion and this is still happening.  They also told my that if the antivirus or anti malware program was blocking any files, I wouldn't have been able to install it in the first place.  So, if anyone has an idea of why the macros keep disappearing, I would greatly appreciate your help with this frustrating issue!

ZTerry
Alteryx
Alteryx

@naterusso 

 

Hi Nate. I hope all is well! Can you please provide me with the best phone number to reach you at? I would like to give you a quick call to get this resolved. 

 

I believe your serial number maybe empty which is causing your user settings to delete anytime you close out Alteryx.

naterusso
6 - Meteoroid

Zach, 

 

Please send me your direct e-mail address in your response so I can e-mail you my phone number.  I was not able to reply to the e-mail that I received. It was bounced back to me. Thanks!

mikes_sellen
5 - Atom

I get an error ("Unable to open file for read") when using the CReW_WildcardXLSXInput tool to read Excel files when those Excel files are opened by other users. Is this expected? Is there a workaround? Thanks!!

 

mikes_sellen_0-1574445444010.png

mikes_sellen_1-1574445474370.png

Julien_B
8 - Asteroid

Here is my situation :
A master workflow (A) using the Runner Tool
The runner tool is configured to launch a workflow (B)
The workflow (B) uses an input tool with a Connection DB alias coming from my ALteryx Gallery (for example : aka:My_Oracle_DB)
When running the master workflow locally : It Works !
When running the master workflow from Alteryx Server : "Impossible to translate alias MY_ORACLE_DB)

It looks like the runner tool is not compatible with workflow using DB alias.

Are you aware of this ? Am I missing something ?

francis_oy
8 - Asteroid

@Julien_B, I think this is a known issue of Alteryx gallery/server. The workaround I had is to use connection string instead of alias in your input tools. In connection string, you could use DSN or DSN less style. You can refer to below post:

 

https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/How-To-format-common-ODBC-DSN-less-...

 

Hope this helps.

 

Regards

Francis