Pages

Monday, 10 February 2020

Building a Better Record ID tool ...


Whilst visiting the Shambles in York, I was treated to a set of ACE inspired ghosts from my friend and fellow ACE, Rafal Olbert.  We searched the community of ghosts at the Ghost Merchant for the perfect trio of goblins to complete the set.  If you notice the coloring, they closely resemble the ACE logo on Raf's polo.  Who except us would spend the time rummaging through hundreds of ghosts to complete this task?
Now comes the question that you might be asking, "What does this have to do with CReW macros?"  It has everything to do with them.  Often there will be a simple task that has to be configured by many in the community.  The time spent in configuring a task could be better spent elsewhere (e.g. visiting York with friends).

There are multiple posts in the Alteryx Community asking how to get unique record IDs on groups of records.  There are also multiple ideas in the community to modify the functionality of the RecordID tool.  James Dunkerley presented at Inspire 2020 on the magic of modifying XML.  One example that he showed was the adding of the grouping functionality to the Record ID.

I grabbed a copy of his macro and have had it waiting for an opportunity to release it to the CReW user-base.  If you think about the construction of a macro that allows for GROUPING, you'll realize that the dynamic nature of grouping requires you to build a solution that allows for multiple (any) selections of fields from the incoming data stream.  James solved the grouping by modifying the XML in a formula:

IIF(IsEmpty([#1]) OR [#1]='""', "<GroupByFields />", 
'<GroupByFields><Field field=' 
+ Replace([#1], '","', '"/><Field field="')
+ '/></GroupByFields>')+ "/*" + [#1] + "*/"

He's modifying the XML of a standard Multi-Row Formula to update the GROUP BY parameter.  When no fields are selected, the resulting xml (grouping) looks like this:

<GroupByFields />

If the incoming data has three (3) fields selected for grouping, the results need to look like this:

<GroupByFields>
   <Field field="Field1"  />
   <Field field="Field2"  />
   <Field field="Field3"  />
</GroupByFields>

The macro is configured to read the incoming metadata and will populate a LIST BOX interface tool with all of the incoming fields.  When the user selects the fields (e.g. Field1, Field2, Field3), the question results are passed to an ACTION tool as a comma delimited series of values.  James then creates a formula to modify the output to produce the XML component displayed above.

If James had attended the BUILD 2 event in Nashville, this was one of the challenges posed.  He would have won that event.

If you'd like to download a copy of the macro, it is in the Alteryx Gallery and can be found here . Any feedback is welcomed.

Cheers,

Mark

Thursday, 30 January 2020

Then Is Not Now


I'm in the U.K. and visiting the Leeds Alteryx User Group.  Just prior to my journey I had generated an idea for Alteryx to improve the generate rows tool:  Please Read & Give Likes.  At the same time I suggested that if you: 1) click on a tool that is on your canvas & 2) go to the pallet and double-click a tool, that the tool will be inserted onto the canvas after the previous tool and that your cursor is moved to the configuration window Please Read & Give Likes.  But now I'm in Manchester and what have I done for CReW?

With a little help from my friends, I decided to tackle joining to date ranges (Tool Mastery: How to Join on Date Ranges).  The question comes up frequently in the community.  What I did was simplify the GENERATE ROWS tool and make it create date or datetime intervals between two end date or datetime points.

If you need to have a record for every day in 2020, you could feed it a start of 2020-01-01 and 2020-12-31 and get 366 output records.  Note:  I just tested and updated the macro.  If you set the TYPE of the output field as datetime and try this, you'll only get 365 records.  Thankfully, I caught this.

So if you want a simple way to generate date records, please look to the Alteryx Gallery for the new macro.  A link is provided below:


Cheers,

Mark

Friday, 18 October 2019

Run away!


A quick note as #Alteryx19 closed in London that should make for a great weekend.  I've been told by Alteryx support  that latest release of Designer 2019.3 – version 2019.3.5.17947 fixes the CReW Macro issue.

Cheers,

Mark

Thursday, 5 September 2019

2019.3 Caution

There are reported issues with the runner macros with 2019.3. Alteryx has been alerted to the issue. At this time I recommend reverting to 2019.2 until Alteryx has an update for us. If you're not using the runner macros, enjoy the many new features.

For more information please reference the linked Alteryx Community article.

Cheers,

Mark

Friday, 30 August 2019

Dynamic Formula: A performance use case

In the Alteryx Community a post caught my attention.  The member wanted to perform calculations based upon the previous field (column).  For the purpose of this post, I've modified the use case as follows:

Within my Alteryx workflow I need to calculate the difference between each numeric column of data (e.g. monthly data) and create "delta" columns that calculate that difference.  Success of the solution depends upon both not needing to update the workflow (e.g. dynamic calculations) and the performance of the solution (not needing to transpose the data).
My friends Thales & Esther (EstherB47recommended transposing the data.

When dealing with this kind of situation, the best you could do is to transpose your columns into rows.
This way you can work with Multi-Row Formula, that can be much more flexible in regards to what you're looking for.

So basically use Transpose Tool and you'll have a better situation to compare previous records against the current ones.
Not me!  I know how to avoid the transpose tool and how to dynamically calculate formulas.  Try this yourself and don't use a Transpose Tool or Multi-Field Formula.  Imagine hundreds of columns with thousand or more rows.  Here's some simple input data:


This is the desired output:



The solution looks like this:

Step 1:  Use a SELECT tool to limit your input to just the "KEY" field(s) plus the numeric fields used in the calculation.  I assume that the key is STRING.
Step 2:  Use a FIELD INFO tool to get the metadata (names & types) of incoming data.
Step 3:  Use a FILTER tool to remove any non-numeric fields.  If the KEY is numeric, we're going to have a problem.  You'd need to use a SAMPLE tool to skip N records (N is the number of key fields).
Step 4:  Configure a FORMULA tool to create "Output Field" names for each of the new fields.
Step 5:  Configure a MULTI-ROW Formula tool to create an expression of:
"[" + ToString([Name]) + "] - [" + ToString([Row-1:Name]) + "]"
Step 6:  Use a sample tool to remove the calculation of Delta from the first record.
Step 7:  Configure the CReW Dynamic Formula tool.

The workflow has been saved within the Community post  (above) and a link is available to the yxmd file (here).

This might make for a clever CReW macro addition.  Is there a volunteer to prototype a generic macro for the community?

Cheers,

Mark

Thursday, 20 June 2019

#Alteryx19 - Nashville: We turned it up to Eleven!

Can you CReW with the best?


As part of #Alteryx19 we conducted not one, but two sold out CReW macro training classes.  With the assistance of @TessaEnns we demonstrated how to gain productivity through the use of CReW macros.  We delved into some alternative methods for getting to the same results, how to open and investigate the macros along with how to get involved with CReW.

Tessa drove and I was the navigator and tour guide for the sessions.  There were some twists and turns as we fielded questions from the class participants.  We explored existing and new use cases that included questions like:  "How can you make the DISTANCE tool execute faster when applying TomTom drive time distances?"  Our answers included options of:  Don't use it when you've previously calculated that distance (save your results),  Overlay a grid to your catchment (trade) area and calculate the distance/times from the center of each grid rather than from the specific household address, Amp up your memory.

Upon return from Nashville, we've added 5 short YouTube videos explaining the following macros:


If you haven't yet subscribed to my (MarqueeCrew) YouTube channel, please do so.

There's been lots of discussion about incorporating CReW functionality into the Alteryx product.  It's no secret that many of the macros would benefit the product and make it easier on new and existing members of #AlterNation.  These ideas have been officially posted to Community and your vote (star) couldn't hurt:

Elevate Crew Macro Pack support from Community based to Officially Supported
Can we add some of the CReW tools and JDunkerly formulae to Alteryx

Even if Alteryx brings all of the macros to life within the product, we'll likely still be here as a proving ground for new innovation.  As a Minimum Viable Product (MVP) or better, the CReW macros assist in making Alteryx more useful and easier to configure.

Hidden in plain view (at the end of this blog) is a note of caution.  It has come to our attention that the Runner Macros do not run on computers with Windows 10 Enterprise.  If you have overcome this obstacle, please let me know.  There are several posts on community about this and we don't currently have plans to address the functionality.

At Inspire we discussed as part of Build2 the creation of new macros.  Maybe you've already created one of these macros:


  • Summary Append:  Merge the functionality of a Summary and Join tool to apply grouping logic and put the result of the summation (e.g. Total by Group) on each incoming record.
  • Record-Row ID:  Extend the RecordID tool to allow to reset to a starting value (e.g. 1) for every record within a group or set.
If you have feedback on these or other ideas, please comment in the blog or reach out to me directly.

Cheers,

Mark



Monday, 14 January 2019

Block All Records

Block All Records

Hold the presses!  The first Alteryx Certified Expert, Jesse Clark has contributed to the CReW macros.  There is simplicity here for all to learn from.  But first, why block all records?  I know that I’ve used the function many times, but how about you?  Have you ever wanted to stop all data from being output?  Essentially, you want to sample zero records.  Oh! That makes everything so clear.  Maybe if I suggest a use case, this will make more sense.

You have an input dataset and want to make sure that the output of your process (e.g. macro) contains all of the fields in the same order as the original file.  You could use the Block All Records macro and stream that into a UNION tool.  Then set the UNION to read #1 first.  It is then up to you whether you will auto-configure by Name or manually configure the data.  The bottom line is that the incoming data fields are in the same order on output.

Another example use case is where you have a list of files that you’ve already read.  If you read this list into a process looking for new files (JOIN with Left output), you might one day want to ignore all read files and rerun the whole set of data.  If you Block All Records, then you’ll end up processing all data as new.

Thanks Jesse for the contribution!

Link:  https://gallery.alteryx.com/#!app/CReWBlockAllRecords/5c3be83f8a933709d4b7f66d

Cheers,

Mark