Pivot Table Alteryx

[AS]

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.

Code	Mode	Num
2343	S	111
2433	P	98090
2496	S	1113
6823	S	43
6823	S	232
6823	S	45465
6823	S	5768
1985	P	33
1985	C	4645
1985	C	6556
1985	C	98
2433	P	3543
2433	P	909
2433	P	433
1985	C	6576
2433	P	7985
2433	C	5656
6823	S	43343
2433	P	878
2433	S	234
6823	S	12121
2433	S	46565
2433	S	565657

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

Code	Mode	Num	Distinct Count of Num
2343	B	111	1
2496	B	1113	1
6823	B	12121	1
		43	1
		232	1
		45465	1
		43343	1
		5768	1
1985	A	33	1
	C	6576	1
		4645	1
		6556	1
		98	1
2433	A	3543	1
		98090	1
		909	1
		433	1
		7985	1
		5656	1
	B	878	1
		234	1
		46565	1
	C	565657	1

Could you guys help me. Thanks in advance.

[atcodedog05]

Hi @AS 

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

Workflow:

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]

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.

Code	Mode	Num	Distinct Count of Num
2343	S	111	1
2496	S	1113	1
6823	S	12121	1
		43	1
		232	1
		45465	1
		43343	1
		5768	1
1985	P	33	1
	C	6576	1
		4645	1
		6556	1
		98	1
2433	P	3543	1
		98090	1
		909	1
		433	1
		7985	1
		5656	1
	S	878	1
		234	1
		46565	1
	C	565657	1

[atcodedog05]

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]

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]

Hi @AS 

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

[atcodedog05 · Accepted solution]

Hi @AS 

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

Workflow:

Hope this helps : )

[AS]

Thanks it worked. 😊

[atcodedog05]

Happy to help : ) @AS