Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Alteryx is here to help you solve your biggest data challenges. Read about the new Virtual Solution Center here.

Error with Columns created by the Cross Tab tool

Highlighted
8 - Asteroid

Hi

 

I created a table using the cross tab tool and am running into issues with one of the columns that got created from the cross tab tool.

 

This workflow is part of an app, and if I use the app functionality, I run into issues. I pasted screens shots of the error message and the tools.

 

Can you please help resolve the issue?

 

Thanks,

Christine

 

cwoo_0-1581639956677.png

 

cwoo_1-1581640030032.png

 

cwoo_2-1581640101023.png

 

17 - Castor
17 - Castor

Hi @cwoo 

 

The columns in the output of the cross tab tool are dependent on the values that are present in the column that's used as the Change Column Headers.  If Sum_17 is not a value in the column, it won't show up as a column.  The way to get around this is the calculate the  L5A SA column before you Cross Tab. 

 

1. Use a filter to get the values that correspond to the Sum_5 - Sum_24 rows.  Something like Name in ("Sum_5", "Sum6",...,"Sum_24").  The filter will only return the rows that exist and, importantly, not error on the missing ones. 

 

2. Add a Summarize tool that groups by all the key columns in your Cross tab to add up the Sum_X values and rename the sum column the same as the column you're using as the Values column in the Cross tab.   

 

3. Use a formula tool to add a column to the output of the summarize called the same as the "Change Column Headers" column and set the formula to "L5A SA"

 

4. Union the output of the summarize with the pre-cross tabbed rows 

 

Now when you cross tab, the "L5A SA" column will appear as a column and contain the sum of the values that exist in your input 

 

Dan

Highlighted
8 - Asteroid

Hi @danilang 

 

Thank you for the response.

 

I took you advice and used a formula tool instead of Cross Tab to get L4Q and L5Y by identifying those quarters using a Period field. 

 

Once I do that, I get correct data for L4Q but all values return 0 for L5Y, which is not correct. 

 

Could you take a look and let me know what I am doing wrong? 

 

Thanks,

Christine

 

 

cwoo_0-1582047569732.png

 

Labels