Only using data from latest append
The dataset I'm using is appeneded to once a day (but at somewhat irregular times), domo has created the columns _BATCH_LAST_RUN_ and _BATCH_ID_ to show this.
I want to create a card using this dataset, but I only want to use data from the latest batch.
What is the best way to do this?
Thanks.
Best Answer
-
There are a handful of ways to achieve this, but almost all of them require a dataflow or blend. My prefered way is via SQL, which also could have a lot of approaches. It can be done via magic etl as well, so I'll show that too.
Here is the logical thought process:
- Get the maximum _BATCH_ID_ value in the dataset (1 record)
- Add a new column (max _BATCH_ID_) to the dataset with this maximum _BATCH_ID_ we just found
- Filter based on _BATCH_ID_ = max_BATCH_ID_. This can be done at the card level or in the dataflow.
SQL
Here is my SQL dataflow approach. This is a very rudimentary approach and not all steps above are visible... but they are happening. I use a subquery which also means I am not using indexes, you may wish do that as a transform all on its own if you have a very large dataset so you can add indexes on _BATCH_ID_.
SELECT a.*
FROM
mytable a
INNER JOIN (SELECT MAX(_BATCH_ID_) AS '_BATCH_ID_' FROM mytable) b
USING(`_BATCH_ID_`)Magic ETL
If you want to go with Magic ETL, this is how I would approach it.
First: [Add Constants] This is not a logical step above, but we need it because of some strangeness to in the Magic ETL tool. Use these settings in the action setup panel:
- I will name my field `JOINER`
- Make it a Whole Number
- Simple value, like 1 or 0
Second: [Group By] Use these settings
- Select JOINER for the grouping
- We will use `max_BATCH_ID_` for the field name
- Use _BATCH_ID_ as the basis for the calculation
- "Maximum" is the aggregate function
Third: [Join Data] Besides steps 1 and 2 in this action block which I'll cover in a second, you also need to pick a join condition with the icon in the middle between 1 and 2. Choose "Inner" if you only want the last batch to appear, Choose "Left Outer" if you want all the data in the output and have the ability for the card to filter to the last batch as needed. Otherwise, here are your settings:
- Pick the [Add Constants] input and `JOINER` for the field.
- Domo will have already added the [Group By] input, you need to pick the `JOINER` field.
Last: [Output Dataset]
If you chose "Inner" join then only the last batch will be output. If you chose 'Left Outer' then you can create a beastmode in the card something like this:
CASE
WHEN `max_BATCH_ID_` = `_BATCH_ID_` THEN 'Last Import'
ELSE 'Old Import'
ENDThen filter the card on that beastmode and select 'Last Import' as the filter option. Here is a snapshot of the layout when it is done...
Good luck! Hope this helps!
**Say "Thanks" by clicking the "heart" in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"1
Answers
-
There are a handful of ways to achieve this, but almost all of them require a dataflow or blend. My prefered way is via SQL, which also could have a lot of approaches. It can be done via magic etl as well, so I'll show that too.
Here is the logical thought process:
- Get the maximum _BATCH_ID_ value in the dataset (1 record)
- Add a new column (max _BATCH_ID_) to the dataset with this maximum _BATCH_ID_ we just found
- Filter based on _BATCH_ID_ = max_BATCH_ID_. This can be done at the card level or in the dataflow.
SQL
Here is my SQL dataflow approach. This is a very rudimentary approach and not all steps above are visible... but they are happening. I use a subquery which also means I am not using indexes, you may wish do that as a transform all on its own if you have a very large dataset so you can add indexes on _BATCH_ID_.
SELECT a.*
FROM
mytable a
INNER JOIN (SELECT MAX(_BATCH_ID_) AS '_BATCH_ID_' FROM mytable) b
USING(`_BATCH_ID_`)Magic ETL
If you want to go with Magic ETL, this is how I would approach it.
First: [Add Constants] This is not a logical step above, but we need it because of some strangeness to in the Magic ETL tool. Use these settings in the action setup panel:
- I will name my field `JOINER`
- Make it a Whole Number
- Simple value, like 1 or 0
Second: [Group By] Use these settings
- Select JOINER for the grouping
- We will use `max_BATCH_ID_` for the field name
- Use _BATCH_ID_ as the basis for the calculation
- "Maximum" is the aggregate function
Third: [Join Data] Besides steps 1 and 2 in this action block which I'll cover in a second, you also need to pick a join condition with the icon in the middle between 1 and 2. Choose "Inner" if you only want the last batch to appear, Choose "Left Outer" if you want all the data in the output and have the ability for the card to filter to the last batch as needed. Otherwise, here are your settings:
- Pick the [Add Constants] input and `JOINER` for the field.
- Domo will have already added the [Group By] input, you need to pick the `JOINER` field.
Last: [Output Dataset]
If you chose "Inner" join then only the last batch will be output. If you chose 'Left Outer' then you can create a beastmode in the card something like this:
CASE
WHEN `max_BATCH_ID_` = `_BATCH_ID_` THEN 'Last Import'
ELSE 'Old Import'
ENDThen filter the card on that beastmode and select 'Last Import' as the filter option. Here is a snapshot of the layout when it is done...
Good luck! Hope this helps!
**Say "Thanks" by clicking the "heart" in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"1
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 620 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 742 Beast Mode
- 58 App Studio
- 41 Variables
- 686 Automate
- 176 Apps
- 453 APIs & Domo Developer
- 47 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 395 Distribute
- 113 Domo Everywhere
- 276 Scheduled Reports
- 6 Software Integrations
- 124 Manage
- 121 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive