community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Community v19.6

Looks aren't everything... But the latest Community refresh looks darn good!

Learn More
SOLVED

Increasing matrix

Hi all,

 

I need to create increasing matrix like this:

Screenshot 2019-03-12 at 23.27.07.png

Basically, the amounts on the intersections are the values attributed to the same category of month, but in different pairs. 

 

I cant put my head around the data structure on the backend before I do crosstab, any advice is highly appreciated.

Alteryx Certified Partner
Alteryx Certified Partner

Hi @alexey_nikityuk 

 

Do you have a sample of your data?

 

I'd like to know how your data comes to be transformed in this.


Cheers,

Highlighted
Pulsar

Hi @alexey_nikityuk

An iterative macro can turn your data into a matrix like the one you have. Here, I'm starting with 2 columns, one with the Month-Year, and one with the value (I just used integers 1-12)

image.png

First, assign a record ID to each row. This will help us crosstab (create the matrix). We're basing the number of times this macro loops on the iteration number (plus one, because the first is considered iteration 0), so the Formula tool creates that field. Then we filter for the first iteration. Grab the first n records, where n represents that iteration number. Those rows are cross tabbed, with that record ID as the column header (to maintain the order of the months). Those headers are renamed with the dynamic rename tool. These records are "written"

Next check if another iteration needs to be run. The summarize tool finds the Maximum Record ID, which is then appended to each row. If the iteration number is less than that Max Record ID, the macro runs again.

Next bunch of rows are sent through the cross tab process, and added underneath the first run. This repeats until all iterations have run.

image.png

Let me know if this works.

Cheers!

Esther

Thank you very much Esther!
Labels