Sum Columns based on Unit Number
Best Answers
-
hm... then I would suggest an SQL Dataflow, putting in something like this pseudocode
select UnitNumber, count(UnitNumber)
from Dataset
group by UnitNumber;
or you might want to sum() up another value column (the above is assuming that it is just about the number of appearances in the dataset). If necessary, you can add your identifying column (if its not UnitNumber) and just join that dataset with your owner dataset directly in the same dataflow. Set the dataflow to update every time your data updates and there will only be a slight delay between your original data source and the dataflow.
This will only make sense for you if you know any SQL, if not there are more instructions about using the dataflow types here.
HTH?
1 -
If you prefer to not use the MySQL redshift transform for this, I would recommend trying out the new "Rank and Window" tile available in the Magic ETLs now.
Knowledge Base Article for Rank and Window action
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman1
Answers
-
If you just need this as a table view, you can just pick unit numbers and hours spent, put both in a table and use SUM in the aggregation field.
If you want to brute-force it, this might help:
Use Beast Mode to make a as many extra columns as you need, for example
Case when `Unit Number`=1 then sum(`Hours`) end
HTH
0 -
I would use this, but after creating the sum columns I need to join this data with another data set, we're trying to match hours worked to specific owners, but the sheet with hours doesnt include the owner so I need to sum hours then join the data, Thanks!
0 -
Is there a missing identifying column? I am not sure why you couldn't join first and then sum up.
0 -
The issue is twofold, firstly there are duplicates, but we need single sum for each car, so if we have unit 5015 listed 30 times we need to sum that into one row, the we need to join the data so we can have the owner identifying column, thanks for the help!
0 -
hm... then I would suggest an SQL Dataflow, putting in something like this pseudocode
select UnitNumber, count(UnitNumber)
from Dataset
group by UnitNumber;
or you might want to sum() up another value column (the above is assuming that it is just about the number of appearances in the dataset). If necessary, you can add your identifying column (if its not UnitNumber) and just join that dataset with your owner dataset directly in the same dataflow. Set the dataflow to update every time your data updates and there will only be a slight delay between your original data source and the dataflow.
This will only make sense for you if you know any SQL, if not there are more instructions about using the dataflow types here.
HTH?
1 -
If you prefer to not use the MySQL redshift transform for this, I would recommend trying out the new "Rank and Window" tile available in the Magic ETLs now.
Knowledge Base Article for Rank and Window action
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman1 -
Awesome, Thanks!
0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 296 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.9K Transform
- 99 SQL DataFlows
- 614 Datasets
- 2.2K Magic ETL
- 3.8K Visualize
- 2.5K Charting
- 727 Beast Mode
- 53 App Studio
- 40 Variables
- 677 Automate
- 173 Apps
- 451 APIs & Domo Developer
- 45 Workflows
- 8 DomoAI
- 34 Predict
- 14 Jupyter Workspaces
- 20 R & Python Tiles
- 394 Distribute
- 113 Domo Everywhere
- 275 Scheduled Reports
- 6 Software Integrations
- 121 Manage
- 118 Governance & Security
- Domo Community Gallery
- 32 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive