Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Cross Tab and Transpose

sagarb
8 - Asteroid

Hello,

 

I have this simple dataset as shown below

MonthIncoming CallsAvg Incoming Call TimeOutgoing callsAvg. Outgoing Call Time
Jan5103015
Feb612405

 

I am trying to get it into this format

MonthCall TypeNo of CallsAvg Call Time
JanIncoming510
Jan Outgoing3015
FebIncoming612
FebOutgoing405

 

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.

13 REPLIES 13
MarqueeCrew
20 - Arcturus
20 - Arcturus

@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:

 

Capture.PNG

Cheers,

Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
sagarb
8 - Asteroid

 

 

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
20 - Arcturus
20 - Arcturus

@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

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
anthony
11 - Bolide

How many words is this picture worth? :)

 

Capture.PNG

 

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
11 - Bolide

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

crosstab.JPG

tkhan
6 - Meteoroid

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
ACE Emeritus
ACE Emeritus
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
6 - Meteoroid

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
ACE Emeritus
ACE Emeritus

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

Labels