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!
The Product Idea boards have gotten an update to better integrate them within our Product team's idea cycle! However this update does have a few unique behaviors, if you have any questions about them check out our FAQ.

Alteryx Designer Desktop Ideas

Share your Designer Desktop product ideas - we're listening!
Submitting an Idea?

Be sure to review our Idea Submission Guidelines for more information!

Submission Guidelines

Dynamic query - deal gracefully with empty record sets

Currently - in workflows which make active use of dynamic queries - if the record set is empty then we end up with errors such as "a record was created with no fields".   This creates issues  when a dynamic query pumps rows out to a macro output, or where dynamic queries go into a join.

 

Could we change the result of the dynamic query so that if it returns an empty record set it still has columns but no rows and therefore doesn't cause errors in workflows?

 

Let me know if we need to provide a worked example?

 

Thank you

Sean

10 Comments
patrick_mcauliffe
14 - Magnetar
14 - Magnetar

While I agree with you that the application should be able to handle this situation better, there is a simple solution that has worked for me.

Right before the dynamic input, add a union and have a single row of dummy data feed into it.  After the dynamic query, have a filter remove the dummy set.

SeanAdams
17 - Castor
17 - Castor

:-) Hey @patrick_mcauliffe - you're absolutely right - I've been doing precisely this (adding dummy rows), thank you for suggesting this!

 

it does work but becomes increasingly messy when the columns of the output are also dynamic (not known at design time)

For example - a macro that copies new rows in table X from database A to database B.  Because the table columns are not yet known at design time, it's painful (doable but painful) to keep on adding dummy rows because that also requires you to add dummy columns and then take them back out again.

 

Again - like you say - you can work around it, but it becomes increasingly messy.

 

 

jdrummey
9 - Comet

+1. I'm doing a lot of JSON data parsing with dynamic columns and it's messy.

bbtak
7 - Meteor

Totally agree with @SeanAdams, in the original post.

 

The dynamic tools should be able to handle empty data sets just the way they handle non-empty ones, or at least have a tick box on them to state if you want it to allow empty result sets without grumping.  The beauty of a tool like Alteryx is that it handles things without worrying about the edge cases. So when it doesn't it is frustrating. My workflows become big enough without additional controls and comments!

 

However, full marks to @patrick_mcauliffe for the above workaround, which is exactly what I need to do to handle the error around my Dynamic Replace tool.  :-)

spainn
9 - Comet

I'm getting that error message when saving a workflow with a dynamic output in-db component to the gallery.  I'm not getting that error during execution but this is because it always returns rows.  I agree with all that is being reported here.  This is a bit shabby and below the usual Alteryx standards!

 

coworker
5 - Atom

Hello, I would appreciate a worked example if you're still offering

 

Dynamomo
11 - Bolide

Does anyone know if this has been fixed?  My client on an earlier version of Alteryx is still having this issue but I'm on 2020.2 and cannot seem to replicate the issue.  When I run a dynamic select and no fields are returned, does not error out.

 

juandiegocuervo
6 - Meteoroid

This has not been fixed in version 2020.2.
Now it shows "You have found a bug. Replicate, then let us know. We shall fix it soon"

Jsurgenor
5 - Atom

Hi, I'm still having trouble with this issue, I tried adding a Union Tool before my dynamic input as suggested, but no data is coming through when i use this set up. I am confused as to how Alteryx is to distinguish between the fields that contain the paths for the files/worksheets that I want to open and the dummy data I added, a worked example of this set up would be very valuable and much appreciated!

 

 My data all follows the same form (ie. same field names and data types), it's just that for some of the files there will be no values (field names are present, data types are specified in MS Excel) and Alteryx is halting processing because of this. I suspect that Alteryx infers the data type from the values, as opposed to what has been specified in the Excel format, so when it comes across empty values it will erroneously assign incorrect datatypes to them and then error due to the data type mismatch, halting processing.

 

 Is there a way to specify what datatypes Alteryx should import the values in each field as, since changing them with a select tool after the dynamic import is not an option? (Since the error occurs during the dynamic import, processing is halted there)

 

 I'm thinking I'll have to union the dummy data into my files manually myself before trying to import them with the Alteryx's dynamic input tool. That way the dynamic input tool will have some values to infer the correct datatypes, and the dummy data can be removed later.

AlteryxCommunityTeam
Alteryx Community Team
Alteryx Community Team
Status changed to: Accepting Votes