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

Increasing matrix

alexey_nikityuk
8 - Asteroid

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.

3 REPLIES 3
Thableaus
17 - Castor
17 - Castor

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,

estherb47
15 - Aurora
15 - Aurora

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

alexey_nikityuk
8 - Asteroid
Thank you very much Esther!
Labels