Crosstab data limited

Question

Currently working with a data set that has columns for a Date, Activity, and a Location. Attempting to cross tab so that I can view the Date unique values as columns, and the Locations as rows, summing the Activity column. When I attempt to set the Date field as the "Create New Column From" option in the Crosstab, it seems to limit me to unique values from the first 5K rows or so, not the unique values through that whole Date column. I've triple checked the data in the column, cleansed it, sorted it, tried all sorts of additional checks on that, and the data going into my crosstab is complete. Even tried limiting my crosstab to a single year, which reduced the original data set from 150K rows to about 43K. Still having the same issue.

nkuipers · Answer

Hello @huffmky,

Thanks for your patience. I have been asking about this internally, but unfortunately have no update to share. Based on the doc for the Cross Tab Tool here, the observed behaviour could be a defect...

"There is no limit to the number of rows or columns that you can pass through Cross Tab."

...but even this is ambiguous, because what is passed through the tool and what is utilized/displayed are two different things. :(

So, I have two suggestions:

* Consider opening a support ticket (i.e., log in to Mission Control at my.alteryx.com and open Case Management, etc.). You can include a reference to this thread; and if possible, please include the dataset for testing.
* In the meantime, you can try splitting the dataset into multiple smaller datasets, Cross Tabbing each, and then Union'ing the result, etc. This is pretty well the textbook definition of "work-around" because it is tedious and inelegant, but at minimum it will allow you to scrutinize the unique dates across all of the data in the tool.

I hope this helps.

Best regards,

Nathanael

huffmky · Answer

It's observed both in the sample results panel and in the final output (I thought the same thing, and added an output to an excel file and the same issue persisted in that output. In the box where I'm selecting the new columns to create, I would assume in the sample scenario that I'd still see all the options available even if I was only looking at a sample in the panel.

nkuipers · Answer

Hi @huffmky,

Can you please clarify: is the limited data observed in the final output (i.e., validated outside of the product), or in the Output in the Sample Results panel of Designer Cloud (i.e., click the Crosstab tool in the canvas, then click Output on the left of the Sample Results panel below)? If the latter: how large is the input dataset? Please let me know.

I am just wondering if perhaps you are being shown a sample of the pivoted data, because the total data after pivoting is >10 MB? (There is an explanation of this behaviour in the doc here.) If so, then you can collect a new sample, or simply try running the job with the current sample and rest assured that the transformations are applied to the entire dataset -- and not just what is shown in Designer.

Does this help?