I don't understand exactly what I'm missing as this seems easy enough.
I'm trying to make the Zone values into their own columns and have the Counts under that..
I'm using the Transpose Tool but every combo isn't giving me the results I need.
Current Data
Counts | Zone | MDT | |
25 | Zone1 | 2023-07-31 01:00:00 | |
39 | Zone2 | 2023-07-31 02:00:00 | |
45 | Zone2 | 2023-07-31 03:00:00 | |
564 | Zone1 | 2023-07-31 04:00:00 | |
65 | Zone2 | 2023-07-31 05:00:00 | |
85 | Zone1 | 2023-07-31 06:00:00 | |
65 | Zone2 | 2023-07-31 07:00:00 |
Goal
Zone1 | Zone2 | MDT |
25 | 0 | 2023-07-31 01:00:00 |
0 | 39 | 2023-07-31 02:00:00 |
0 | 45 | 2023-07-31 03:00:00 |
564 | 0 | 2023-07-31 04:00:00 |
0 | 65 | 2023-07-31 05:00:00 |
85 | 0 | 2023-07-31 06:00:00 |
0 | 65 | 2023-07-31 07:00:00 |
You want cross tab, not transpose.
Group on MDT, set Zone as Column Headers and Counts with Sum as the values and you should get your desired output. Just need to store and move the columns a bit.
I always feel like the Imputation tool is under valued. This is a excellent simple use case.
I rarely use the Imputation tool and always tends to use the Cleansing tool
Thanks for showing a good use case of the Imputation tool. 😁
User | Count |
---|---|
18 | |
14 | |
13 | |
9 | |
8 |