Pivot tables

mroker
mroker Member
in Variables

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?

Tagged:

Best Answer

  • rahulrampa
    rahulrampa Member
    edited September 10 Answer ✓

    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.

    image.png

Answers

  • rahulrampa
    rahulrampa Member
    edited September 10 Answer ✓

    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.

    image.png

Categories