Hi Community, I was creating a pivot table on my dataset and i've used Summarize tool to build the Pivot where i did Group by with Name, Strategy, Product Group, Book and did Sum for the rest of data set. But, unlike in excel i am not getting data in presentable format as I see duplicates in all the group by fields as below. NameStrategyProduct GroupBookSum New BusinessSum_EditSum_FXVikas PasrichaHY28TRSMMTC12030Vikas PasrichaHY28TRS3HGY22131Vikas PasrichaHY28TRSRESL32232Vikas PasrichaHY29TRSMDSS42333Vikas PasrichaHY29TRSMRTE52434Vikas PasrichaReservesNA Deriv1HYM62535Vikas PasrichaReservesNA Deriv2HYE72636Vikas PasrichaReservesNA Deriv3HGY82737Asish ChinariHY34CD Index TrancheMMTC92838Asish ChinariHY34CD Index TrancheRESL102939Asish ChinariHY35CD Swap IndexMRTE113040Asish ChinariHY35CD Swap Index2HYE123141Asish ChinariHY35CD Options3HGY133242 However, I want my data to be representable as below wherein i should not see duplicates and have unique data which looks more clean and representable. Can we create data in the below format in Alteryx? I was thinking of using Unique feature but not sure it will work. Is there any other way if possible or we can't create excel like Pivot in Alteryx. NameStrategyProduct GroupBookSum New BusinessSum_EditSum_FXVikas PasrichaHY28TRSMMTC12030 3HGY22131 RESL32232 HY29TRSMDSS42333 MRTE52434 ReservesNA Deriv1HYM62535 2HYE72636 3HGY82737Asish ChinariHY34CD Index TrancheMMTC92838 RESL102939 HY35CD Swap IndexMRTE113040 2HYE123141 CD Options3HGY133242 Thanks to community for their assistance as always ! Best Regards Vikas

Remove Duplicate fields in PIVOT

Hi Community,

I was creating a pivot table on my dataset and i've used Summarize tool to build the Pivot where i did Group by with Name, Strategy, Product Group, Book and did Sum for the rest of data set. But, unlike in excel i am not getting data in presentable format as I see duplicates in all the group by fields as below.

Name	Strategy	Product Group	Book	Sum New Business	Sum_Edit	Sum_FX
Vikas Pasricha	HY28	TRS	MMTC	1	20	30
Vikas Pasricha	HY28	TRS	3HGY	2	21	31
Vikas Pasricha	HY28	TRS	RESL	3	22	32
Vikas Pasricha	HY29	TRS	MDSS	4	23	33
Vikas Pasricha	HY29	TRS	MRTE	5	24	34
Vikas Pasricha	Reserves	NA Deriv	1HYM	6	25	35
Vikas Pasricha	Reserves	NA Deriv	2HYE	7	26	36
Vikas Pasricha	Reserves	NA Deriv	3HGY	8	27	37
Asish Chinari	HY34	CD Index Tranche	MMTC	9	28	38
Asish Chinari	HY34	CD Index Tranche	RESL	10	29	39
Asish Chinari	HY35	CD Swap Index	MRTE	11	30	40
Asish Chinari	HY35	CD Swap Index	2HYE	12	31	41
Asish Chinari	HY35	CD Options	3HGY	13	32	42

However, I want my data to be representable as below wherein i should not see duplicates and have unique data which looks more clean and representable.

Can we create data in the below format in Alteryx? I was thinking of using Unique feature but not sure it will work. Is there any other way if possible or we can't create excel like Pivot in Alteryx.

Name	Strategy	Product Group	Book	Sum New Business	Sum_Edit	Sum_FX
Vikas Pasricha	HY28	TRS	MMTC	1	20	30
			3HGY	2	21	31
			RESL	3	22	32
	HY29	TRS	MDSS	4	23	33
			MRTE	5	24	34
	Reserves	NA Deriv	1HYM	6	25	35
			2HYE	7	26	36
			3HGY	8	27	37
Asish Chinari	HY34	CD Index Tranche	MMTC	9	28	38
			RESL	10	29	39
	HY35	CD Swap Index	MRTE	11	30	40
			2HYE	12	31	41
		CD Options	3HGY	13	32	42

Thanks to community for their assistance as always !

Best Regards

Vikas

Output

Macros

Accepted answers

IraWatt

Hey @Vikas27,

I've attached a workflow which I think is inline with the logic above

If you want any explanation on how the multi row formula work make sure to ask

HTH,

Ira

Multi Row form.yxmd

All comments

IraWatt

Hey @Vikas27,

I've attached a workflow which I think is inline with the logic above

If you want any explanation on how the multi row formula work make sure to ask

HTH,

Ira

Multi Row form.yxmd

Vikas27

Hi Ian,

Thanks for the response. I ran your Workflow i'm getting the desired result. However, when i try to run the same on my dataset i am facing an issue -

a) When I run first Multi-Row Formula i am getting name getting corrected where i see duplicates removed.

b) BUT when I add 2nd Multi-Row Formula in Strategy tab i) Strategy tab see no impact at all and secondly the result which is coming is my results in Name get amended with Strategy and i see Strategy in my results in Column Name. However, there is no change in Strategy column.

BUT same is not happening in your WF. Do you know why it's happening.

Thanks.

Vikas27

@IraWatt - Please ignore i made some goof up at my end. Thanks for all your help I got the resulted as expected but i don't understand the Multi-Row formula. Can you please help me understand what i have done and what this function help us achieve

I'll accept your solution.

Thanks,Vikas

IraWatt

Hey @Vikas27,

No worries ! XD

IF [Row-1:Right_Name]= NULL() THEN [Name] 
ELSEIF [Row-1:Right_Name]=[Right_Name] THEN NULL()
ELSE [Right_Name] ENDIF

I have the first multi row formula code above. Before the formula I joined the records with each other on their position essentially creating a duplicate for every column (new columns pre appended with right). The reason I did that was to ensure as the multi row updates each row I still have a duplicate Name/strategy/ect to refer to.

Multi row formula work row by row applying logic (if my explanation doesn't makes sense maybe be best to watch the Alteryx community video on multi row formula too). The first multi row tool updates the value in the name column.

The first line checks if the duplicate is NULL, if yes it will leave the name column as is. If duplicate (right_Name) of the row above then the Name column is updated with NULL. Then finally if they don't match then just use the duplicate name.

Its a bit difficult to explain possibly the best approach is to check Alteryx's video and then go row by row trying to apply the logic in the formula to understand it.

Hope some of that made sense,

Ira

Quick Links

This months top contributors

AkimasaKajitani 388

mceleavey 388

mbarone 337

Hollingsworth 335

LanisC 335