SOLVED
Moving data for a record from multiple rows to a single row
Options
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
JMoriarty65
7 - Meteor
‎01-03-2024
06:51 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
I have a set of data that has a semi repetitive format that i would like to move from having multiple rows per record to a single row, but i always struggle with transpose and crosstab. Not sure if this can be done, any help would be appreciated.
This is the current format
Evaluation Date | Evaluation Score |
12/8/2023 8:46 | 100 |
Contract Number | 571789878 |
12/19/2023 13:53 | 100 |
Contract Number | 500005404 |
12/19/2023 14:37 | 0 |
Contract Number | 571078974 |
Was the transaction processed accurately | taxes were withheld for state of NC. No taxes should have been withheld |
Was the transaction processed accurately errors | taxes should not be withheld for a 1035 exchange. |
This is the format i would like
Evaluation Date | Evaluation Score | Contract Number | Was the transaction processed accurately | Was the transaction processed accurately errors |
12/8/2023 8:46 | 100 | 571789878 | ||
12/19/2023 13:53 | 100 | 500005404 | ||
12/19/2023 14:37 | 0 | 571078974 | taxes were withheld for state of NC. No taxes should have been withheld | taxes should not be withheld for a 1035 exchange. |
Solved! Go to Solution.
Labels:
- Labels:
- Datasets
- Transformation
- Workflow
2 REPLIES 2
17 - Castor
‎01-03-2024
07:15 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @JMoriarty65
Here's one way:
- Assign an ID to each record, I did this by looking for the date format in the first column.
- Separate the data between the evaluation date/score and the other 3 fields
- Cross tab the other 3 fields and join back
‎01-03-2024
08:14 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Thanks for the help!
