Transforming data and summing

[LincolnMike]

I have the following:

vin	date	Section	Code	Store	Hrs
1234	2017-06-01	A	XX	B224	0.01
1234	2017-06-01	B	YY	B224	1.50
1234	2017-06-02	C	ZZ	B224	0.05
5678	2017-06-03	A	XX	A112	0.01
5678	2017-06-03	B	VV	A112	1.10
9012	2017-06-04	A	XX	C666	0.01
9012	2017-06-04	B	MM	C666	0.08
9012	2017-06-30	C	ZZ	J123	0.05

Using Alteryx, how do I have the output look like this:

vin	SecA	SecB	SecC	SecA_Dt	SecB_Dt	SecC_Dt	SecA_St	SecB_St	SecC_St	TotHr
1234	XX	YY	ZZ	2017-06-01	2017-06-01	2017-06-02	B224	B224	B224	1.56
5678	XX	VV		2017-06-03	2017-06-03		A112	A112		1.11
9012	XX	MM	ZZ	2017-06-04	2017-06-04	2017-06-30	C666	C666	J123	0.14

[LincolnMike]

Thanks again...I will play with this and keep you posted.  I am being dragged away to start a back splash project in my kitchen.  :-|

[SeanAdams]

Hey @LincolnMike

Take a look at the way that I've split the data set that needs to be reformatted vs. grouped.   This is a common pattern that we find ourselves using often - doing a different prep on subsets of the data and then joining it all back together again.

Good luck with the Back Splash project!

[LincolnMike]

Hi Sean,

thanks for responding.  Could you re save the  "TransformingData.yxmd" you created so I could open it in Alteryx 10.6?

Thanks.

Mike

[SeanAdams]

No problem Mike,

The file I attached was already backed down to version 9.5 so you should be OK using Alteryx 10.6.

Let me know if you have trouble - attached again.

Sean

[LincolnMike]

Sean, thanks again for trying to re-save, but I still cannot open the file.  I get the same error message: "This document created by a more recent version of this application and cannot be read"

I will try your solution too based on your screenshot.

take care

[LincolnMike]

Hi,

the solution you 2 provided me worked perfect for the data.  Now I have a new data set that does not associate as well with the solution you previously provided m.

I have this for my data:

vin	rprDt	Store	LbrCd	ReprOrder	Tot_Hrs_pd_forVIN
123	2016-09-30	AX12	E	ABC	1.8
123	2016-09-30	AX12	M	ABC	1.8
123	2016-09-30	AX12	A	ABC	1.8
789	2016-11-07	GG22	A	DEF	1.9
789	2016-11-08	GG22	E	DEF	1.9
789	2016-11-09	ZZ33	N	XYZ	1.9

This is the desired output, with Concatenating Repair Order to Labor Cd:

vin	RprDt1	RprDt2	RprDt3	Store1	Store2	Store3	RprOrd_LbrCd1	RprOrd_LbrCd2	RprOrd_LbrCd3	Tot_Hrs_Pd_ForVIN
123	2016-09-30	2016-09-30	2016-09-30	AX12	AX12	AX12	ABC_E	ABC_M	ABC_A	1.8
789	2016-11-07	2016-11-08	2016-11-09	GG22	GG22	ZZ33	DEF_A	DEF_E	ZYZ_M	1.9