Here's my solution: Transpose & count rows where non-zero. Find your keys and filter (using a join).
I'd suggest using a Cross Tab and then a Multi-Row Formula:
Once you have all your months in a single column do a running count of months with >0, then filter down to just the records that hit 3 or more. Join it back to your data to get the original records and you should be good to go.
Not totally sure if this gets at exactly what you want, but it might help you down the right path(attached).
EDIT: The actual math in my sample workflow will not necessarily be a unique ID, but might help you use your formulas.
Ex: (2014yr)*365 + (01m)*30 + (31d) = 735171
and (2014yr)*365 + (02m)*30 + (01d) = 735171
You can just take the number of days from an arbitrary point...
We have just announced this week the opening of the Alteryx Product Certification Program.
Please see the below link for further information. You’ll find a timeline for different certification levels, a list of resources available, structure of the exams, and more helpful facts:
Not a way to output the SQL query that failed that I know of. Without more information about your workflow and the Dynamic Input tools configuration, it’s challenging to suggest possible workaround.
If you’re using ODBC, there is an option to log the queries in the ODBC Config Manager (not in Alteryx), but this is manually turned on and off.
If you change the prior formula to the following it should meet that need:
IF CONTAINS([_CurrentField_],'PM') THEN REPLACE([_CurrentField_],'PM',DATETIMEFORMAT(DATETIMEADD(DATETIMETODAY(),-1,'months'),"%b'%y")) ELSEIF CONTAINS([_CurrentField_],'CM') THEN REPLACE([_CurrentField_],'CM',DATETIMEFORMAT(DATETIMETODAY(),"%b'%y")) ELSE [_CurrentField_] ENDIF
Basically, this way we will only replace the 'PM' or 'CM' values in your field names, rather than replacing the entire thing.
I believe @Garrett suggestion would still be the proper approach for what you're trying to achieve. Using the wildcards in the file path only looks at the file name, not the sheet name. However, one common issue I see (which may be the reason it's not working for you) is if there are schema variations from one file to the next. Even if it looks like every sheet has the same amount of columns and same headers, It's possible that a column that was attributed as a TEXT in the first file, but read in as a NUMBER in the next, which fails the matching schema rule.
If this sounds like it could be the issue you're seeing, best option would be to create a batch macro that can process each file separately and then combine them in the final output. What makes this option work that you're able to tell the output of the batch macro to ignore the schema rule and just output the results by their name or position (like the options you have in a UNION tool).
Hope this helps!
The schema is the arrangement of data types, data field size, column names, and column order. So basically, it's what you think it is. I would guess that there is some very subtle difference among your files that is insignificant to you as a human, but significant to Alteryx. I would recommend that you input each file individually, and then look at the metadata for each. See if the metadata tables match.