Cross Tab and Transpose

[sagarb]

Hello,

I have this simple dataset as shown below

Month	Incoming Calls	Avg Incoming Call Time	Outgoing calls	Avg. Outgoing Call Time
Jan	5	10	30	15
Feb	6	12	40	5

I am trying to get it into this format

Month	Call Type	No of Calls	Avg Call Time
Jan	Incoming	5	10
Jan	Outgoing	30	15
Feb	Incoming	6	12
Feb	Outgoing	40	5

I thought it was going to be a simple calculation but I am not able to get it into the desirable format. I am using Transpose but I guess I am not using it the right way. Thanks for your help.

[MarqueeCrew · Accepted solution]

@sagarb,

Here's a solution.  Please review the posted solution.  If I get time, I'll edit this post explaining why I solved it this way.  A picture is worth a 1000 words:

Cheers,

Mark

[sagarb]

Thank you for your reply.  It's amazing that you did it in 15 mins when I was trying to do the same thing for an hr. Thanks again.

[MarqueeCrew]

@sagarb,

I'm humble about the time, so hopefully my head doesn't explode.  I rushed the solution without explanation because I was doing this between phone calls.  I saw the post and waited to see if someone would jump in.  When they didn't, then I responded.

I recognized that you were actually transposing two times and re-assembling the call (regardless of type) data into rows.  I pictured the process that I would perform manually and then translated it into Alteryx.  I've been a user for practically 15 years.

Thanks for using the community.  One day I hope to see you answering posts too.  Remember, you don't have to be first with a solution.  Your unique way of solving a problem might be something that inspires others to re-think their approach.  Often friends in the community will post solutions and I'll have an aha moment.  

Cheers,

Mark

[anthony]

How many words is this picture worth? :)

Hey Alteryx, this tool needs some UX love ... it solved a problem for me awhile back, by shear luck and perseverance. Even getting this output was a challenge as I had to keep deleting 'columns' because you cannot edit once created.

Here is the xml if someone wants to see how setup - create above workflow and drop this xml into D'arrange tool.

<Configuration>
<KeyFields orderChanged="False">
<Field name="Month" />
</KeyFields>
<OutputFields>
<Columns>
<Column name="Call Type" type="text" context="Take from Field Names" />
<Column name="No of calls" type="combobox" />
<Column name="average call time" type="combobox" />
</Columns>
<Data>
<r>
<c>Incoming Calls</c>
<c>Incoming Calls</c>
<c>Avg Incoming Call Time</c>
</r>
<r>
<c>Outgoing calls</c>
<c>Outgoing calls</c>
<c>Avg. Outgoing Call Time</c>
</r>
</Data>
</OutputFields>
</Configuration>

[alex]

Another way - less manual than arrange and without having to use the select tool.

[tkhan]

I try to use a formula after using Crosstab, 

but it looks like the formula cannot see the new columns without the workflow running once. 

So if I have 3 new columns that are generated as a result of the cross tab.

i can create the formula, and it works

I close the workflow, open it again, and it cannot see those columns,

please advise.

Thank you

[BenMoss]

Irrespective of if your formula is throwing an error along the lines of ‘this field does not exist’ providing it is one you know will pull through it will still run fine, you dont need to delete the formula tool, run the workflow and start again.

Ben

[tkhan]

Thanks Ben

So the issue i guess is not at the Formula part, its before that, where I am using the Data Cleansing function to clean out all the null values in the new columns.

When I run the workflow, the data cleansing shows the new transposed columns. However, when i exit and open the workflow again, the columns dont show in the data cleansing function.

Now when i run the workflow, it runs, and the new columns now appear again, but the data cleansing window have those columns unchecked.

Please advise,

Thanks again

[BenMoss]

What's the purpose of the data cleanse tool you are using? Is it applying to all fields?

Could you cleanse prior to the cross-tab (though I am aware some of the cleansing might be as a result of what the cross-tab does with your data, for example converting NULLs to EMPTY.

Ben