Tuesday, 16 August 2022

CReW Expect Records, or "What to expect when you're expecting data"

 This morning I awoke to a WhatsApp message from my friend, @grossal (Alex) asking the following:

[5:12 AM, 8/16/2022] Alex Gross: Is there a CReW "Expect Records" Macros? 

I see a lot of time Use Cases, e.g. Database Connections or files where I need to check if records come in. I think this could also be useful for the SHA256 Macro.

I just had the scenario where I connected to a database and got zero records back - but it didn't crash my workflow because the metadata was still provided by the db.

Alex had already explored the macro updates to either create a cloned copy of the existing macro or to include functionality to choose between expect 0 or expect something.  It would be easy.  

Easy is in the eye of the beholder.  I knew that I'd have to change the image and that change would complicate everything.  I also don't want to interfere with any existing use of the macro.  I opted for another macro.  Of course, I'd opt for Alteryx to include an ERROR condition on tools that they should allow for an ERROR if 0 records are passed through them.

Another vertical workflow!  Instead of using a COUNT RECORDS macro, I opted for using the JOIN tool (configured with join by record position).  When 0 records are present in the I (Input) anchor, then the "Error Message" data is passed to the MESSAGE tool.  That tool is configured as an "ERROR" and passes the contents of the TEXT INPUT tool to the Results panel.

This method is ever so slightly more performant.  But more importantly it doesn't include another macro inside of it.  That simplicity appealed to me.  When I can use fewer tools and less macros (oxymoron) I think that I've got a winner.  After testing with and without AMP and testing just to see that it works I am confident enough to let you, my friends check it out too.

You can find the macro in the Alteryx Community (Gallery).  Right now I'm getting ready for Alteryx & Beer with Alex and the German Alteryx Community.



P.S. During the Alteryx & Beer session, I was coerced into creating an output anchor for the macro.  If data is present (e.g. NOT an error), the data will pass through the macro.  This was an ask and so it was made to happen.

P.P.S  If you have a LARGE set of data, the "Count Records" method will literally count every record.  This method only requires 1 record to know if things are kosher or not.

No comments:

Post a Comment