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 |
Solved! Go to Solution.
Hi @LincolnMike, Find the approach that I would use attached.
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.
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
Hi,
I still get an error trying to open
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.
oops sorry...mborriero...not Bolide. [meteor!!!]
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!