Input Data Tool - select criteria

[wiggot]

All:

(Alteryx Designer 11.0 x64)

My current issue is that I'm trying to set up a very large data query from a couple of tables with tens of millions of records. I've set my resource optimization down to 1mb. I've set the Record Limit for the tool to 100 rows. I've set the global record limit to 100 rows.

But, no matter what I do, the moment I make a change to my Input Data Tool for this massive table, Alteryx hangs up for about 15 minutes while it attempts to load/preview/whatever the entire table.

The Input Data Tool allows me to set ONE condition for a criteria. So, for example, I can say I want all units with unit_status <> 'IA'. That works fine.

But, I can't figure out how to set criteria conditions beyond a single option. For example, I only need data for 17 out of 51 jurisdictions - is there a way to set this limit at the Input Data Tool level, so that Alteryx doesn't hang as much? I've tried "IN". I've tried <> "XX" and <> "XX" and <> "XX", etc. Neither has worked. I've built the select query in Access so that I know it's valid SQL, copied it and tried to paste it into the SQL editor and that doesn't work. Alteryx tells me there's a problem with the select query every time.

This can't really be this limited. Can it??? Why am I forced to wait 15 minutes for Alteryx to hang before I can set a filter to further limit the data?

[Claje]

Hi,

Would you be able to copy the SQL error that you are getting when you copy the Access SQL over?  It is possible there is a small syntax issue causing this, but seeing the error will help a lot.

You can definitely set pretty complex filters in the Input Data tool using the SQL editor.

By default I think the editor includes the ability to set multiple "OR" statements, so one option would be to set up all of your criteria using the "OR" statement, and then change the "OR" to an "And" in the SQL Editor.

You should also be able to use an "IN" statement if you want, although I think that requires using the SQL Editor.

Finally, the In-Database tools might be worth looking at.  You can select your table and fields in the Connect In-DB tool, then use a Filter In-DB to create your filter, and use the Data Stream Out so that you can bring the data down into Alteryx.  If you aren't a SQL expert, this is probably the best option, as it requires the least SQL knowledge to make work and is pretty quick.

[wiggot · Accepted solution]

I can't reproduce the not-working SQL. I don't know... I manually typed everything out, in the visual builder as well as in the SQL editor, and it didn't work.

I went over to Hyperion, ran the query, copied the query log, fixed up punctuation and just pasted it into the SQL editor and it worked just fine. Same SQL. Makes no sense. All of sudden I was able to use IN clauses in the Select Tool, and Betweens and everything else that I had tried to put in manually. Didn't work until I copied and pasted it in from somewhere else.

Is there some aspect of the visual builder that doesn't translate correctly, where normal SQL works just fine if you switch off of the visual builder and only use the SQL editor? I've seen other mentions to this effect.