ETL/SQL or Beast Mode Data Merging Assistance

Hi,

I need some assistance regarding the best way to perform the following. We have a report which is updated quarterly. On this report are numerous metrics of different types (strings, numbers, etc.). We need to be able to create a recursive dataset where we can (in HTML Table view) see a side-by-side comparison of the latest 4 quarters and some additional columns shared across the dataset.

In the past, we just created a separate dataset for each quarter but I feel this defeats the purpose. Additionally, we now have API access to our product and can now automate sending data into Domo.

What I've tried so far (testing 2 quarters only):

I was able to create a recursive dataset using ETL and removing duplicates based on two columns (metric_id and last_measurement_date). This worked fine in one card where none of the data for the most recent quarter had been submitted yet after using beast mode and CASE WHEN to filter the dates for the two columns.

attempt 1.png

When I tried this with data that had been updated, I realized there were duplicate rows.

attempt 2.png

I guess my question is, what way should I be doing this? I'm not great with SQL but can do standard joins. The only two columns in this dataset where values should change are the last_measurement_date, current_metric_rating, and most_recent_value. All other columns including the metric name, metric ID, low, moderate, and high columns should not change.

Happy to provide more info if needed. Thanks in advance for the help.

Find more posts tagged with

Comments

jaeW_at_Onyx

it's unclear to me what you mean the rows are duplicate.

does the underlying data actually contain duplicate rows? or do you mean, you have more cards in your visualization than you expected?

If it's the later, i'd guess you have Date in Sorting (or a similar period based column)

I'm guess that because adding a field to the ORDER BY will also add it to the GROUP BY clause in SQL thus generating rows for each period instead of collapsing the data to one row for each Metric (and using your columns to show the desired results).