Pivot Data in ETL

I have dataset i am trying to reformat using a pivot table. I cant use the pivot chart because it only shows measures as values so i am trying to reorganize my data using pivot in etl. My data includes Facility, Date, and Task Completed (yes/no). Beow on left is how my data currently is and on right is how I want to set it up. Could someone assist? I am trying the pivot and unpivot tiles but having some trouble.
Current | To Be | |||||
---|---|---|---|---|---|---|
Facility | Date | Task Cmpleted | Facility | 1/1/2024 | 1/2/2024 | |
Facility 1 | 1/1/2024 | Yes | Facility 1 | Yes | Yes | |
Facility 2 | 1/1/2024 | Yes | Facility 2 | Yes | Yes | |
Facility 3 | 1/1/2024 | Yes | Facility 3 | Yes | Yes | |
Facility 4 | 1/1/2024 | Yes | Facility 4 | Yes | Yes | |
Facility 5 | 1/1/2024 | No | Facility 5 | No | Yes | |
Facility 1 | 1/2/2024 | Yes | ||||
Facility 2 | 1/2/2024 | Yes | ||||
Facility 3 | 1/2/2024 | Yes | ||||
Facility 4 | 1/2/2024 | Yes | ||||
Facility 5 | 1/2/2024 | Yes |
Best Answer
-
@TMonty0319 I'm not aware of a systematic way to do this. However, I also don't recommend using Magic ETL to pivot date fields for a few reasons:
- This eliminates the date field, which means you can no longer use date-based filtering (This Week, YTD, etc.)
- Every new date would add another column, which you would then have to add to your card
Have you considered changing your "Task Cmpleted" column to have binary 1 and 0 values in the dataset's current structure so that you have a numeric value for a pivot table card?
1
Answers
-
@TMonty0319 I'm not aware of a systematic way to do this. However, I also don't recommend using Magic ETL to pivot date fields for a few reasons:
- This eliminates the date field, which means you can no longer use date-based filtering (This Week, YTD, etc.)
- Every new date would add another column, which you would then have to add to your card
Have you considered changing your "Task Cmpleted" column to have binary 1 and 0 values in the dataset's current structure so that you have a numeric value for a pivot table card?
1
Categories
- All Categories
- Product Ideas
- 2.1K Ideas Exchange
- Data Connections
- 1.3K Connectors
- 309 Workbench
- 17 Cloud Integrations
- Data & ETL
- 2.3K Magic ETL
- 120 SQL DataFlows
- 667 Datasets
- Visualize & Apps
- 90 App Studio
- 198 Pro-code Components
- 2.6K Charting & Analyzer
- 873 Calculations & Variables (Beast Mode)
- AI & Data science
- 23 Domo AI & AI Chat
- 4 Managing AI
- 18 Jupyter Workspaces
- Automate
- 122 Workflows
- Alerts
- Distribute
- 118 Domo Everywhere
- 284 Reporting
- Manage
- 145 Governance & Security
- 489 APIs
- 11 Add-ins & Plugins
- 13 Domo Community Gallery
- 49 Product Releases
- 13 Domo University
- Community Forums
- 41 Getting Started
- 31 Community Member Introductions
- 118 Community Announcements
- 5K Archive