Exclude header row and Totals row from dataset
Hi,
Is there a way to exclude main header row and Totals row from a dataset?
I have connected a SharePoint file to domo, the dataset has main and sub header row, totals row. I want the sub header to be shown as a main header in the dataset when I build the cards.
There is Totals row at the end I want to exclude this row as well.
Kindly advise.
PM | Period | ||||
Name | Codes | Location | Created Date | Due Date | End Date |
MP | 1002 | AUS | 11/3/2020 | 1/1/2021 | 1/3/2021 |
GD | 1003 | NYC | 11/2/2020 | 1/2/2021 | 1/3/2021 |
NV | 1004 | JPN | 12/1/2020 | 1/1/2021 | 1/5/2021 |
TS | 1005 | LDN | 12/5/2020 | 1/5/2021 | 1/6/2021 |
Totals | 4 |
Thanks!
Best Answer
-
use Magic ETL 2.0.
I assume your data arrives with generic column names "column 1", "column 2", "column 3"
add FILTER tile to exclude rows where "column 1" = ...
you'll need 3 sets of filter rules to exclude the first row, the second row and the row containing Total.
then use the ALTER tile to rename tiles and set the column types to Date, Integer etc, b/c they'll all arrive as type text.
Jae Wilson
Check out my 🎥 Domo Training YouTube Channel 👨💻
**Say "Thanks" by clicking the ❤️ in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"2
Answers
-
exclude (FILTER) rows where column = "totals" and column 3 is null.
ideally you'd be able to configure your file connector to ignore row 1 and set row 2 as the header. is that an option?
otherwise you may have to also filter out row 2, and then set the remaining rows using a SELECT statement and then set the DATA TYPE with an ALTER COLUMNS tile.
Jae Wilson
Check out my 🎥 Domo Training YouTube Channel 👨💻
**Say "Thanks" by clicking the ❤️ in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"0 -
Hi @jaeW_at_Onyx ,
I have connected the file using SharePoint connector and the data gets updated on daily basis, so I cannot use File upload connector.
""filter out row 2, and then set the remaining rows using a SELECT statement and then set the DATA TYPE with an ALTER COLUMNS tile.""" - Can you please elaborate - also, the only row headers my final dataset should include are Name, Codes, Location, Created Date, Due Date, End Date?
Thanks!
0 -
use Magic ETL 2.0.
I assume your data arrives with generic column names "column 1", "column 2", "column 3"
add FILTER tile to exclude rows where "column 1" = ...
you'll need 3 sets of filter rules to exclude the first row, the second row and the row containing Total.
then use the ALTER tile to rename tiles and set the column types to Date, Integer etc, b/c they'll all arrive as type text.
Jae Wilson
Check out my 🎥 Domo Training YouTube Channel 👨💻
**Say "Thanks" by clicking the ❤️ in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"2 -
Thanks @jaeW_at_Onyx !
0
Categories
- All Categories
- 1.7K Product Ideas
- 1.7K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 294 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.8K Transform
- 97 SQL DataFlows
- 607 Datasets
- 2.1K Magic ETL
- 3.8K Visualize
- 2.4K Charting
- 707 Beast Mode
- 49 App Studio
- 39 Variables
- 667 Automate
- 170 Apps
- 446 APIs & Domo Developer
- 44 Workflows
- 7 DomoAI
- 33 Predict
- 13 Jupyter Workspaces
- 20 R & Python Tiles
- 391 Distribute
- 111 Domo Everywhere
- 274 Scheduled Reports
- 6 Software Integrations
- 115 Manage
- 112 Governance & Security
- Domo Community Gallery
- 31 Product Releases
- 9 Domo University
- 5.3K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 103 Community Announcements
- 4.8K Archive