Parse and Concatenate

[mmaziere]

Hello,

I have a table that has properties and values that I want to parse out, group, sum and then put back together again as a single string . I attempted to use the Cross Tab tool, but it only gets me part way to the solution. In the following example data I want to consolidate all of the possible values in the 'PROP' column, sum up the VOL and WT values and drop out the ITM records. However, the ITM values are not just limited to a handful, but rather could be in the hundreds or even thousands.

SH	DOC	DG	ITM	FREQ	PROP	VOL	WT
3000877930	1000327984	1	10	2DW	O_K	1.756	14.9
3000877930	1000327984	1	90	2DW	O_R	4.25	27.78
3000877930	1000327984	1	30	2DW	O_K	0.33	8
3000877930	1000327984	1	70	2DW	O_P	4.87	23
3000877930	1000327984	1	50	2DW	O_K	0.5	10

Desired Result:

Group SH, DOC & DG (As KEY), FREQ

Sum VOL & WT

SH	DOC	DG	KEY	FREQ	PROP	VOL	WT
3000877930	1000327984	1	300087793010003279841	2DW	[O_K] [O_R] [O_P]	11.71	83.68

[binuacs]

@mmaziere 

[Qiu]

@mmaziere 
Try some different. but seem longth than the one of @binuacs 😁

[Raj]

One way of doing this.

[PangHC · Accepted solution]

least tools challenge
first summarize the group and sum, formula to add key, then 2nd summarize for group, concatenate, and final sum. 

if summarize have concatenate distinct. it will down to 2 tools. :)

here the idea link: Concatenate Unique - Alteryx Community

time efficiency (move key after 2nd summarize as it will less record. and use select tool to move column) 

 

[mmaziere]

thanks everyone!