Remove Duplicate fields in PIVOT

[Vikas27]

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 

[IraWatt · Accepted solution]

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

[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

[Vikas27]

Actually, you have explained it beautifully and i can understand from the IF Condition making perfect sense. But yeah will see Alteryx video as i want to understand primarily how and where all i can use this function. As i'm not from coding background so takes time to grasp as i was not aware or familiar with the Multi-row function.

But yeah, that's why we have lovely Alteryx community who is there to help. Anyways, thanks a lot ! Appreciate your help on the issue. :)

[IraWatt]

No worries, thanks @Vikas27 ! XD