Illegal field name when using dbt_utils.pivot

Archive
1

how can i use the pivot functionality to create a pivot table? i have read the fishutils documentation but i don’t understand how i can use it for my purpose. i have two date columns , one of which i want to convert to the column headers and the other as the row ‘headers’. The content i want is for it to aggregate the values of one column in this case, ‘volume’ but how can i do this using dbt?

select
  repayment_month,
  {{ dbt_utils.pivot('Repayment_Due_Month',dbt_utils.get_column_values(ref('trades'), 'Repayment_Due_Month')) }}
from {{ ref('trades') }}
group by repayment_month

when i run this it does not work i get a : Illegal field name: 2020-04-01 error. Also i don’t understand how i can specifiy to aggregate the volume??

any one know?

2

My hunch here is that it’s complaining because you can’t set a column name with a digit as the first letter, which is going to happen since you’re pivoting on a date column. Maybe try creating a new sub query that appends something like “d2020-04-01” and then pivoting on that?

1 Like
3

Your hunch is correct, the column labels field has to have valid column names for pivot to work. A potential future enhancement for the macro is to provide a column prefix or column rename ability; in the meanwhile creating an intermediate CTE to rename field values is a good workaround.

1 Like
4

i created a cte where i appended d to the front of the values, but i still get the error: Illegal field name: d2020-04-01, i’ve checked the column type and all values are string

5

I think hyphens are banned too, try replacing the dashes with underscores.

6

thank you this works! however my values in the table are not being summed up but instead counted, how can i get it to sum? i understand that the agg argument default is sum, but when i replicate this pivot table in data studio to check and select metric on my volume column to ‘count’ it matches what the output of dbt model above is. To add my table only contains: repayment month, repayment due month, and volume , i would like the content of pivot table to show sum of volume, instead it is showing the counts

7

I think your second argument might be incorrect? I don’t know what the original table structure is, but it looks like in the original code snippet your second argument to pivot is a date column and not a numeric.

8

when i change it to volume i get : Illegal field name: 900? sorry which argument do you mean… as the get column values would be setting every numeric field in volume as column header no?

9

Have you tried using the prefix argument ? I think that would help this error.

select
  repayment_month,
  {{ dbt_utils.pivot('Repayment_Due_Month',dbt_utils.get_column_values(ref('trades'), 'volume'),
  prefix='column_prefix_') }}
from {{ ref('trades') }}
group by repayment_month