Forum Discussion
Pivoting to get the correct data format for visuals
Hi,
Struggling with the right format of my data. I have 2 tables (case values and case value attributes). The case values are the names of the fields and the case value attributes are the values. I need to know the names of the fields to know what the values refer to so I have merged them.
Which gives me a table like this
| case id | case attribute ID | Case attibute name (database name) | Field name | Value |
| 1 | 3364 | NYP_HIH_001 | Type of referral | CSE |
| 1 | 3365 | NYP_HIH_002 | Ref assessment | High |
| 1 | 3366 | NYP_HIH_003 | Date of referral | 1/11/2020 |
| 1 | 3367 | NYP_HIH_004 | Intial contact date | 05/11/2020 |
What I really want is the format below - classic excel format of col headers and data underneath, where the field name is the column header and its value underneath...i can delete the columns I dont need but the format is hard to achieve...
| case id | Type of referral | Ref assessment | Date of referral | Initial contact date |
| 1 | CSE | High | 01/11/2020 | 05/11/2020 |
I have lots of calcs to do between columns to form number of days taken, average days to do x, column x plus Column Y - Column Z etc etc etc and this classic excel format seems the best way to do it as all the data is then in one table as well (hence why I merged them at the beginning.
I have been trying the pivot table function in the Transform tab but end up with this
| case id | case attribute id | Type of referral | Ref assessment | Date of referral | Initial contact date |
| 1 | 3364 | CSE | 0 | 0 | 0 |
| 1 | 3365 | 0 | High | 0 | 0 |
| 1 | 3366 | 0 | 0 | 01/11/2020 | 0 |
| 1 | 3367 | 0 | 0 | 0 | 05/11/2020 |
But this doesnt work for calcutaing number of days between date of referral and initial contact date is showing as 0 for the case 1 as as no value.
Any advice on how best to do the pivot or to get the data in the format I require? Thanks in advance for any advice!
Remove the 2 columns
case attribute ID Case attibute name before the Pivot.
Then Pivot on the Field Name, using Value as value. Choose 'Don't aggregate' from advanced.
