Input Data Tool - select criteria

Question

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?

wiggot · Accepted Answer

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.