community
cancel
Showing results for 
Search instead for 
Did you mean: 

Engine Works Blog

Under the hood of Alteryx: tips, tricks and how-to's.
Alteryx
Alteryx
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
 

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 time before.  It is very useful when updating a 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 a 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 which 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
Principal Software Engineer, Tech Lead Core Engines

Adam Riley is a Principal Software Engineer and the Tech Lead of the Core Engines Team at Alteryx. He started on his Alteryx journey as an Alteryx user before joining the company in 2011. He writes a personal blog about Alteryx at www.ChaosReignsWithin.com and is the former curator of the Crew Macro Pack.

Adam Riley is a Principal Software Engineer and the Tech Lead of the Core Engines Team at Alteryx. He started on his Alteryx journey as an Alteryx user before joining the company in 2011. He writes a personal blog about Alteryx at www.ChaosReignsWithin.com and is the former curator of the Crew Macro Pack.

Comments
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?

Alteryx
Alteryx

@ryeldell Yes they do

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

Alteryx
Alteryx

@ben_broom Are you running 32 bit windows?

No, I am running 64 bit windows
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 Smiley Happy

 

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.

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

childA_no_errors_messages_in_parent_are_seen_as_conversion_errors.PNGChild 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?

error from child A run shown in parent workflow log parser.PNGparent log parser shows child A error

It'd be great with any ideas

 

Thanks

 

 

 

 

Alteryx
Alteryx

You are very close Smiley Happy

 

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?

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?

 

 

Alteryx
Alteryx

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

 

 

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?

Alteryx
Alteryx

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?

Meteor

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

Alteryx
Alteryx

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

 

 

 

2017-04-27 09_54_54-fw-dev-alteryx - Remote Desktop Connection.pngWindows 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!

 

 

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 Smiley Happy

 

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

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

@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

Hi @AdamR

 

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.

Alteryx
Alteryx

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

Thanks @AdamR - really appreciate the quick response.

 

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

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!

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.

 

 

 

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

Asteroid

Hello @AdamR,

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

 

 

Alteryx
Alteryx

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

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?

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

Alteryx Partner

Hello @AdamR
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? 

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.

Labels