Pivot tables

mroker

I dunno why, but some of the time I cannot wrap my head around pivot tables. Here is what I want to do and here is how my data is structured and the column headers….

Account | A Prime_TV Revenue | B Prime_TV Revenue | A Premium_TV Revenue | B Premium_TV Revenue

Pivot table I want…

TV Category | Company A. | Company B

Prime_TV. | $500000 |. $6000000

Premium_tv. |. $55000. |. $5000

Is this possible given the data structure that i have?

rahulrampa

since there are only 4 columns you can directly use a SQL dataflow to convert the data in the format you want and avoid any pivot stuff in ETL:

select Account, 'A' as company, 'Prime_TV' as TV Category, A Prime_TV Revenue  as value from Table

union all

select Account, 'A' as company, 'Premium_tv' as TV category, A Premium_TV Revenue  as value from Table

union all

select Account, "B" as company, "Prime_TV" as TV category, B Prime_TV Revenue as value from Table

union all

select Account, "B" as company, "Premium_tv" as TV category, B Premium_TV Revenue  as value from Table

In the above code, Table is the name of the datasource where this data is stored. Once you have the data in the above format, you can just create a pivot table card and drag 'company' field to Columns and 'TV category' field to Rows and the 'value' column to Values and you should get the data in the format you want in a card.

rahulrampa

since there are only 4 columns you can directly use a SQL dataflow to convert the data in the format you want and avoid any pivot stuff in ETL:

select Account, 'A' as company, 'Prime_TV' as TV Category, A Prime_TV Revenue  as value from Table

union all

select Account, 'A' as company, 'Premium_tv' as TV category, A Premium_TV Revenue  as value from Table

union all

select Account, "B" as company, "Prime_TV" as TV category, B Prime_TV Revenue as value from Table

union all

select Account, "B" as company, "Premium_tv" as TV category, B Premium_TV Revenue  as value from Table

In the above code, Table is the name of the datasource where this data is stored. Once you have the data in the above format, you can just create a pivot table card and drag 'company' field to Columns and 'TV category' field to Rows and the 'value' column to Values and you should get the data in the format you want in a card.