Dynamic query - deal gracefully with empty record sets

[SeanAdams]

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

[patrick_mcauliffe]

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]

:-) 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]

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

[bbtak]

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]

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]

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

[Dynamomo]

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]

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]

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.