Comparing Data Sets

If I have a data set on an inventory of items and a second data set on the sale of those items, how might I compare those data sets with Domo to discover 1) which items in our inventory have never been sold and 2) the average length of time before items added to our inventory are sold?
Thanks!
Best Answer
-
In order to compare datasets and build cards in Domo, you'll need to combine the datasets in meaningful ways. There are a few different ways to combine datasets including DataFlows, Datafusion, and Magic ETL. Each has its own strengths depending on your level of comfort with SQL. For your points, I can imagine a solution in DataFlow involving the following:
1. In order to see items that have never sold, you can do an SQL LEFT JOIN. Something along the lines of:
select a.* -- all the columns from our inventory table
from inventory a
left join sales b on a.item = b.item
where b.item IS NULL
Basically, we're trying to match the two tables on item numbers, but in this case, we're also adding a filter to only leave inventory items where there is no match in our sales table.
2. This one is a little more involved and depends a little on having the right columns in your datasets, but one solution could look like the following:
select
a.item, a.item_created_date, MIN(b.sales_date) as 'first sales date'
from inventory a
join sales b on a.item = b.item
group by 1, 2
In this case, we're again joining the "inventory" and "sales" tables, but we're then reducing the dataset to be unique by item (the `group by 1, 2` clause). What we're joining from the sales table is the "sales_date" and we're returning the earliest (i.e. MIN) sales_date for the item. This leaves us with a dataset with all the information we need to get what you want. At the card level, I could plot the "item_created_date" along with the "first sales date" column. We could also create a beast mode column that was the length of time between the two dates: DATEDIFF(`item_created_date`, `first sales date`)
2
Answers
-
In order to compare datasets and build cards in Domo, you'll need to combine the datasets in meaningful ways. There are a few different ways to combine datasets including DataFlows, Datafusion, and Magic ETL. Each has its own strengths depending on your level of comfort with SQL. For your points, I can imagine a solution in DataFlow involving the following:
1. In order to see items that have never sold, you can do an SQL LEFT JOIN. Something along the lines of:
select a.* -- all the columns from our inventory table
from inventory a
left join sales b on a.item = b.item
where b.item IS NULL
Basically, we're trying to match the two tables on item numbers, but in this case, we're also adding a filter to only leave inventory items where there is no match in our sales table.
2. This one is a little more involved and depends a little on having the right columns in your datasets, but one solution could look like the following:
select
a.item, a.item_created_date, MIN(b.sales_date) as 'first sales date'
from inventory a
join sales b on a.item = b.item
group by 1, 2
In this case, we're again joining the "inventory" and "sales" tables, but we're then reducing the dataset to be unique by item (the `group by 1, 2` clause). What we're joining from the sales table is the "sales_date" and we're returning the earliest (i.e. MIN) sales_date for the item. This leaves us with a dataset with all the information we need to get what you want. At the card level, I could plot the "item_created_date" along with the "first sales date" column. We could also create a beast mode column that was the length of time between the two dates: DATEDIFF(`item_created_date`, `first sales date`)
2
Categories
- 10.5K All Categories
- 6 Connect
- 916 Connectors
- 250 Workbench
- 460 Transform
- 1.7K Magic ETL
- 69 SQL DataFlows
- 476 Datasets
- 186 Visualize
- 250 Beast Mode
- 2.1K Charting
- 11 Variables
- 16 Automate
- 354 APIs & Domo Developer
- 88 Apps
- 3 Workflows
- 20 Predict
- 5 Jupyter Workspaces
- 15 R & Python Tiles
- 245 Distribute
- 62 Domo Everywhere
- 242 Scheduled Reports
- 21 Manage
- 42 Governance & Security
- 170 Product Ideas
- 1.2K Ideas Exchange
- 10 Community Forums
- 27 Getting Started
- 14 Community Member Introductions
- 55 Community News
- 4.5K Archive