Why Is the Pivot Tile not Working as I Expected

Options
Roman_K
Roman_K Member
in SQL DataFlows

I am trying to use the Pivot tile to accomplish the following (in my attached dataset), but am getting no results under the column I am creating (Weekend_Date_Pivot).

Weekend Date should now be columns with Demand_Qty as the metric under each Weekend Date

Inventory - Example File.xlsx

Best Answers

  • MichelleH
    MichelleH Coach
    Answer ✓

    @Roman_K In the Pivot tile, you will need to specify a value of WEEK_END_DATE in step 4 for every column you want displayed in the final dataset.

    I don't recommend pivoting by date in Magic ETL for a few reasons:

    1. It is cumbersome to add every possible date column when creating the dataflow
    2. It requires constant upkeep as your data updates, you will need to manually add new date columns to the ETL as new data populates
    3. The output dataset no longer contains a usable date field, so you lose the ability to filter the dataset by date
    4. Similar to #2, as the ETL continues to add columns, any cards you build with the output dataset will need constant upkeep to display the new columns

    Instead, I recommend skipping the Pivot tile in the ETL and displaying the output data in a Pivot Table card. This will preserve the native filter functionality of the WEEK_END_DATE field and require minimal maintenance.

  • ColemenWilson
    ColemenWilson Coach
    Answer ✓

    There isn't, but you could reduce the available data on the pivot table card using filters or pre-aggregate the data in an ETL.

    If I solved your problem, please select "yes" above

Answers

  • ColemenWilson
    ColemenWilson Coach

    Could you share screenshots of your ETL? The pivot tile details.

    If I solved your problem, please select "yes" above

  • Roman_K
    Roman_K Member
    edited January 6
    image.png

    Hi. Thank you for providing direction, it is much appreciated. Here are the details of my pivot tile, but the column I'm creating (Demand_Qty by Week_End_Date) is still empty for all rows.

    image.png
  • Roman_K
    Roman_K Member

    Is it not working properly perhaps because an ItemNumber can have multiple SKUs?

  • MichelleH
    MichelleH Coach
    Answer ✓

    @Roman_K In the Pivot tile, you will need to specify a value of WEEK_END_DATE in step 4 for every column you want displayed in the final dataset.

    I don't recommend pivoting by date in Magic ETL for a few reasons:

    1. It is cumbersome to add every possible date column when creating the dataflow
    2. It requires constant upkeep as your data updates, you will need to manually add new date columns to the ETL as new data populates
    3. The output dataset no longer contains a usable date field, so you lose the ability to filter the dataset by date
    4. Similar to #2, as the ETL continues to add columns, any cards you build with the output dataset will need constant upkeep to display the new columns

    Instead, I recommend skipping the Pivot tile in the ETL and displaying the output data in a Pivot Table card. This will preserve the native filter functionality of the WEEK_END_DATE field and require minimal maintenance.

  • Roman_K
    Roman_K Member

    Thank you for your suggestion, I tried it, but am getting the message that it's too much data (over 100k rows). Is there a way to accommodate larger data sets in a Pivot Table card?

  • ColemenWilson
    ColemenWilson Coach
    Answer ✓

    There isn't, but you could reduce the available data on the pivot table card using filters or pre-aggregate the data in an ETL.

    If I solved your problem, please select "yes" above

Categories