Beast Mode

TMonty0319
TMonty0319 Member
in Beast Mode

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

Tagged:

Best Answers

  • Sean_Tully
    Sean_Tully Contributor
    Answer ✓

    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
    ColemenWilson Coach
    edited August 2 Answer ✓

    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.

    If I solved your problem, please select "yes" above

  • nmizzell
    nmizzell Contributor
    Answer ✓

    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.

Answers

  • Sean_Tully
    Sean_Tully Contributor
    Answer ✓

    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
    ColemenWilson Coach
    edited August 2 Answer ✓

    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.

    If I solved your problem, please select "yes" above

  • nmizzell
    nmizzell Contributor
    Answer ✓

    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
    nmizzell Contributor

    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

Categories