Beast Mode

TMonty0319

How do i do a beast mode formula to get the response value of a record that was completed most recently? If this is my data i want to just take the bottom row. My groupings cant be changed in magic etl just need to take response value with highest or most recent time

Name	Location	Time	Response value
Ben	A	2:07PM	5
John	A	3:55PM	15

Sean_Tully

A couple of solutions using Magic ETL - 1) Use a window function to rank the records in descending time, then look for rank 1 in your card; 2) Do a group by to find the max time, then cross join that back to your data so that you can find the most recent time in a beast mode, where the Time field equals the Max Time field.

Maybe there's a way to do this with a fixed function beast mode, but I'm not certain.

ColemenWilson

Create a beastmode and use it as a filter:

CASE WHEN `Time` = MAX(`Time`) THEN 'In' ELSE 'Out' END

Not sure what format your time field is in, but you'll need to make sure it is in a datetime format for this to work.

nmizzell

Hey TMonty,

You will need to create a conditional column:

case when `timestamp` = max(`timestamp`) over() then 'Yes' else 'No' end

However, this will require that you have a timestamp column.

If you do not have a timestamp column, you can make one by appending the date and time columns and converting to timestamp format.

Sean_Tully

A couple of solutions using Magic ETL - 1) Use a window function to rank the records in descending time, then look for rank 1 in your card; 2) Do a group by to find the max time, then cross join that back to your data so that you can find the most recent time in a beast mode, where the Time field equals the Max Time field.

Maybe there's a way to do this with a fixed function beast mode, but I'm not certain.

ColemenWilson

Create a beastmode and use it as a filter:

CASE WHEN `Time` = MAX(`Time`) THEN 'In' ELSE 'Out' END

Not sure what format your time field is in, but you'll need to make sure it is in a datetime format for this to work.

nmizzell

Hey TMonty,

You will need to create a conditional column:

case when `timestamp` = max(`timestamp`) over() then 'Yes' else 'No' end

However, this will require that you have a timestamp column.

If you do not have a timestamp column, you can make one by appending the date and time columns and converting to timestamp format.

nmizzell

Once your conditional column has been created, you can filter to only include the 'Yes' values, which will have only the most recent response for that category