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
- 10.5K All Categories
- 8 Connect
- 918 Connectors
- 250 Workbench
- 472 Transform
- 1.7K Magic ETL
- 69 SQL DataFlows
- 477 Datasets
- 198 Visualize
- 254 Beast Mode
- 2.1K Charting
- 11 Variables
- 17 Automate
- 354 APIs & Domo Developer
- 89 Apps
- 3 Workflows
- 20 Predict
- 5 Jupyter Workspaces
- 15 R & Python Tiles
- 247 Distribute
- 63 Domo Everywhere
- 243 Scheduled Reports
- 21 Manage
- 42 Governance & Security
- 178 Product Ideas
- 1.2K Ideas Exchange
- 12 Community Forums
- 27 Getting Started
- 14 Community Member Introductions
- 55 Community News
- 4.5K Archive