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

[mborriero]

Hi @LincolnMike, Find the approach that I would use attached.

[LincolnMike]

Thanks Bolide, but unfortunately for me I have Alteryx Version 10.6 and the .yxmd file you sent will not open for me.  My company will not upgrade to a newer version until late this year.

[mborriero]

Can you try now?

[SeanAdams]

Hey @LincolnMike 

Congratulations on your first post, and welcome to the community!

This definitely is possible using a combination of transpose & crosstab.

Summary here:

- Transpose the data to switch columns into rows

- Then you can rename the data as you need

- then transpose it back again but only using VIN to drive the crosstab

- This doesn't give you the total hours though - so do a separate flow to calculate the sum by VIN and add that to the data stream later on using a join.

Hope this helps @LincolnMike

If this solves your question, would you mind marking it as solved to add to the knowledge base - or if you have followup questions, feel free to continue on this thread.

Have a good Sunday

Sean

[LincolnMike]

Hi,

I still get an error trying to open 

[mborriero]

Give it last try... It should work now.

[LincolnMike]

Thanks for your help Bolide and Sean.

I was able to open the last file Bolide sent and it is very close to my solution.  I will work with the last 3 columns to create a sum Total and show you my final.

Take Care.

[LincolnMike]

oops sorry...mborriero...not Bolide. [meteor!!!]

[mborriero · Accepted solution]

It's fine do not worry :)

I did not read that you wanted hours summed and not split.

You can modify the workflow using a Summarize tool for the Hours ColumnGlad you were able to solve your problem!