Dev Space

Customize & extend the power of Alteryx. SDKs, APIs, custom tools, and more!
SOLVED

Pivot Table Alteryx

AS
8 - Asteroid

Hi Experts,

Facing issues in creating some pivot view. I tried with summarize activity but unable to make logic and create this view. Values as shown blow.

CodeModeNum
2343S111
2433P98090
2496S1113
6823S43
6823S232
6823S45465
6823S5768
1985P33
1985C4645
1985C6556
1985C98
2433P3543
2433P909
2433P433
1985C6576
2433P7985
2433C5656
6823S43343
2433P878
2433S234
6823S12121
2433S46565
2433S565657

 

Expected result would be - also if there are 3 mode in particular Mode column I need to maintain Mode sequence as A, B, C

CodeModeNumDistinct Count of Num
2343B1111
2496B11131
6823B121211
  431
  2321
  454651
  433431
  57681
1985A331
 C65761
  46451
  65561
  981
2433A35431
  980901
  9091
  4331
  79851
  56561
 B8781
  2341
  465651
 C5656571

Could you guys help me. Thanks in advance.

8 REPLIES 8
atcodedog05
20 - Arcturus

Hi @AS 

 

To get this kind of output we need to use multi-row formula tool and convert below rows to blanks.

 

Workflow:

atcodedog05_0-1626677366092.png

 

1. Using sort tool to sort by code and then mode to bring it in order.

2. In multi-row formula tool using groupby to keep only 1st row and convert all to blank.

3. Using select tool to rename and keep only required columns.

 

Hope this helps : )

 

AS
8 - Asteroid

Hi @atcodedog05 

Thanks for replying again. but sorry I changed the mode.. the correct Mode was P, S, C. so there is no ascending format.so how can I set the sequence let me know. Expected result would be.

   

CodeModeNumDistinct Count of Num
2343S1111
2496S11131
6823S121211
  431
  2321
  454651
  433431
  57681
1985P331
 C65761
  46451
  65561
  981
2433P35431
  980901
  9091
  4331
  79851
  56561
 S8781
  2341
  465651
 C5656571

 

atcodedog05
20 - Arcturus

Hi @AS 

 

It needs to sorted by ascending or descending for the rows to be grouped together. Or else it wont be displayed properly.

AS
8 - Asteroid

so you are saying there is no another way, activity, and logic to set this particular sequence P,S,C. I need this sequence because later I need to apply Xlookup

atcodedog05
20 - Arcturus

Hi @AS 

 

If you have a predefined sequence we can definitely sort on that to maintain desired order.

atcodedog05
20 - Arcturus

Hi @AS 

 

You can do something like this to maintain the desired sequence.

 

Workflow:

atcodedog05_0-1626679094070.png

 

Hope this helps : )

AS
8 - Asteroid

Thanks it worked. 😊

atcodedog05
20 - Arcturus

Happy to help : ) @AS