Finding the last non-zero value in each column

DataSquirrel

I've got a DataSet with several columsn. Each column stores a single statistic:

 

TotalItems

NewItems

RepairItems

 

There are many rows, each of which has each column along with data, location name and other values of interest.

 

Here's the question: Sometimes rows have 0's that indicate 'no change'. What we're after is a chart that uses the last non-zero value in each column as the data for the plot. There's a timestamp in each row, so it's not hard to figure the order and what "last" might mean. 

 

The problem here is that we're not going to find the right value for each column in the same row. So, I can just get the last row. Also, the value won't always be the MAX or else I could use that. 

 

Is there a clever beast mode that might help out here, or do I need to rework my DataSet?

 

Thanks!

BlueRooster

Yes, you'll need a SQL transform.

 

Something like:
SELECT * FROM table 

ORDER BY `Timestamp`

LIMIT 1

 

That will give you the most recent row of data

 

DataSquirrel

Possibly as a related quesiton, how do you select only the latest (by timestamp) row for a plot? So, if we've got 5,000 rows, only the last 1 is used for the chart.

 

Thanks again!

BlueRooster

So if I understand you correctly, you're wanting to return the last "Non-zero" value found in each column based on the timestamp you have in a column. 

 

If I were going to tackle this one in my own environment, I would do it in a SQL transform.

The pseudocode would look something like this:

SELECT `Identifier`,

      (SELECT `Col1` FROM table WHERE `Col1` <> '0' ORDER BY `Timestamp` DESC LIMIT 1) AS 'Col1'

FROM table

 

Obviously, continue repeating the subquery for each column you're trying to find the last non-zero value for.

 

Then you can use the result of that transform as your base for your chart.

 

Hope this helps. Let me know if you have any questions.

 

Sincerely,

ValiantSpur

 

**Please mark "Accept as Solution" if this post solves your problem
**Say "Thanks" by clicking the "heart" in the post that helped you.

ST_Superman

You could start by filtering out the zero values and then sort the data by your timestamp field in descending order.  That should give you your most recent data first.  

 

If you could provide a bit of your data, I may be able to give a more in depth answer.

DataSquirrel

Great answer, thanks for the reply! Given that, I'd probably be better off biulding a one-row data set to push into Domo. The code will be similar to what you propose, but we're currently aiming to keep most of our data prep logic outside of Domo. I know, that's not the Domo way...but it's working well for us so far. Staying open minded though...

DataSquirrel

As it turns out, this is what I actually need to do today. Just find the most recent row. Our data set is mostly used to find _trends_, but sometimes we want to find _status_. The current/latest day gives us a snapshot of the current status. I don't see anything like a TOP or LAST function in the Beast Mode reference, or any kind of ORDER BY. Is this (finding the most recent row) another case for a SQL Transform?

BlueRooster

Yes, you'll need a SQL transform.

 

Something like:
SELECT * FROM table 

ORDER BY `Timestamp`

LIMIT 1

 

That will give you the most recent row of data

 

DataSquirrel

Thanks! I havn't tried SQL Transforms, so here's a good excuse to try them out. That will put me in a better place to decide if we should prep an extra DataSet on the outside or do it in Domo. 

 

Thanks again for all of the help!