Alteryx Designer Desktop Discussions

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

How do I Cross tab/Pivot with two columns?

7 - Meteor

I'm trying to create a pivot table from a sales table I have. The sales table has invoices, and there are several invoice lines per invoice. There are also several "types" of different "Amounts" per invoice line. I am trying to create a pivot table that maintains 3 columns worth of data (Type, Amount and Description). 


I mocked up some sample data below that looks kind of like my dataset:


11BobCost50This is a note for this
11BobTax5Here we are
11BobSurcharge3Ok, this is it
12BobCost75And this is a note for this
12BobSurcharge40But here is another note
13BobCost50Yup, another
13BobTax7.5Ok, this is last
13BobSurcharge3Nope, this is
21PhilTax33And yet another
31JoeSurcharge20Can't stop the notes



Is it possible to create something like this:


Invoice_NbrInvoice_Line SumDescription
11Cost50 This is a note for this
  Surcharge3 Here we are
  Tax5 Ok, this is it
 2Cost75 And this is a note for this
  Surcharge40 But here is another note
  Tax0Ok, this is last
 3Cost50 Yup, another
  Tax3 Nope, this is


7 - Meteor

I made a workflow (attached), but clearly doing something wrong.

12 - Quasar

Hi @ajr2183


Here's my go at it. I find it a lot easier to do with the basic table. You then need to apply some column rules to get the repeating numbers to disappear. Not the most intuitive, but it works.






7 - Meteor

Wow, that's awesome! Thank you! I'm going to need to examine how those multi-row formula tools work a bit more, since this is exactly what I was trying to achieve. I really appreciate it.


7 - Meteor

Hi Kat,


Thank you again so much for your help.


Would you know how to add a row at the top of each new Invoice_Nbr sequence, which I could Highlight in a dark gray, and which is basically like the Top-level summary row... For example, it would have the Invoice_Nbr, nothing for Invoice_Line, nothing for Type, then it would have the Sum of the Amounts, and nothing for Description. I would then use that as kind of a visual break between Invoice_Nbrs, but also use it as a summary row for that invoice that I could Make a darker gray and bold the text. I tried doing this with a summarize tool, and a formula tool to make Invoice_Line = 0, then sorted ascending on Invoice_Line, but when I get to the Multi-Row Formula tool, I just cannot figure out how to make it work.


Maybe you have an even more efficient way to achieve this, but any guidance in general would be amazingly appreciated.