selecting last row in beastmode

IVG
IVG Member
in Magic ETL

So I have a dataset which has a column with a value that updates every 10 minutes. I would like to make a comparative gauge that will compare last value from today vs the last value from yesterday - i can't figure out the two beastmodes to create those values... any ideas? Thanks in advance!!!

Best Answer

  • IVG
    IVG Member
    Answer ✓

    ok so I had a colleague write some SQL that made it work... so I will show you here just as a point of interest...

     

    SELECT (
    SELECT Value
    FROM dataset_1

    WHERE date = (
    SELECT min(date)
    FROM dataset_1

    WHERE date > DATE_SUB(NOW(), INTERVAL 24 HOUR)
    )
    ) as YESTERDAY,
    (
    SELECT Value
    FROM dataset_1
    WHERE date = (
    SELECT max(date)
    FROM dataset_1
    WHERE date > DATE_SUB(NOW(), INTERVAL 24 HOUR)
    )
    ) as TODAY

    FROM dataset_1
    LIMIT 1

     

    So this took care of all the issues - the result was that I got two values one from today and one from exactly 24hours ago in two separate columns - which made it perfect for using in a comparative guage!

Answers

  • PodiumMason
    PodiumMason Contributor

    Hi @IVG

     

    Referencing the documentation in the knowledge base: http://knowledge.domo.com?cid=comparativegauge, I think this could be possible:

     

    I think what I would do is a couple date beast modes to align the data so for both yesterday and today we're looking at the same date:

     

    Date filter to filter on dates less than now: Case when `datetime` <= Now() then 'Yes' else 'No' END

    Date alignment (order by this column descending): Case when date(`datetime`) = Current_date() then `datetime` else  Date_add(`Datetime`, Interval 1 Day) end

     

    Current value on the gauge will be the value column. 

    Previous value will be a beast mode: Case when date(`Datetime`) = date_add(Current_date(),Interval -1 Day) then `Value column` else 0 END

     

    Filter card to today. Current value value will be the `Value Column`, Previous value will be the `Previous Value` beast mode mentioned above.

     

    Hope this is helpful for a direction to go, this is just the first thing that came to mind when you described your problem, let me know if it works or if you have addtional questions.

     

    Best of luck!

    **Say 'Thanks' by clicking the thumbs up in the post that helped you.
    **Please mark the post that solves your problem as 'Accepted Solution'
  • IVG
    IVG Member

    Either I am doing something incorrectly or this is not working...

    I have created the 3 beastmodes you have suggested the problem i get a the current value as the last value in the "Value Column" but the Previous Value column is empty...

     

    I think its' becuase the filter for today's value cuts out all the previous values so the there are no values from before "today"... 

     

    but not sure... Thanks for the help, but still, I have no idea how to do it right ?

  • PodiumMason
    PodiumMason Contributor

    @IVG Maybe try removing the today filter on the card and see if that works? With the ordering of the data, that might fix it not showing the previous values.

    **Say 'Thanks' by clicking the thumbs up in the post that helped you.
    **Please mark the post that solves your problem as 'Accepted Solution'
  • IVG
    IVG Member

    now the previous value column is showing the same value as the current value column, i am hopelessly confused ...

  • IVG
    IVG Member

    I can't imagine this being such a difficult thing to do - all i want to select is most current value and one last value from a previous day... its seems like there should be a very simple way to do it.

     

    As an alternative I am ok with selecting the latest current value and one 24 hrs before it... 

     

    Please help!

     

  • PodiumMason
    PodiumMason Contributor

    @user00421 

     

    I figured out the problem with my logic, the bad news is that the comparitive gauge won't accept the fix. 

     

    The date alignment column is correct, the current value needs to be a beast mode:

     

    SUM(Case when date(`DateTime`) = CURRENT_DATE() then `Value` else 0 END)

     

    and the Previous value needs to be aggregated:

     

    SUM(Case when date(`DateTime`) = date_add(current_date, Interval -1 day) then `Value` else 0 end)

     

    This gets the data in the correct format when the data is ordered by datetime descending. (See screenshot)

     

    2018-02-23_12h39_25.pngThe data is in the correct format at the table card level, but when I try to order by the date alignment in the comparative gauge card and use the pre-aggregated values, I get an error, unable to load the card. It seems that the card type doesn't agree with aggregated values.

     

    I can't seem to get around this in card builder. Doing this outside card builder would be fairly simple, but that's not the answer you were asking for.

    **Say 'Thanks' by clicking the thumbs up in the post that helped you.
    **Please mark the post that solves your problem as 'Accepted Solution'
  • IVG
    IVG Member

    So my problem is that once I follow your advise and set it up and have a beastmode for Current Value and Beastmode for Previous Value - I get the followign table...

     

    essentially I get a column with current values that correspond to todays date that appear in that date's row and same for previous value but since the numbers are not in the same row it is not working...

     

     

    Screen Shot 2018-02-23 at 2.57.27 PM.png

  • PodiumMason
    PodiumMason Contributor

    @IVG That's exactly right. The only way I know to line up the values is by aggregation but it seems the comparitive gauge card doesn't support beast mode level aggregation.

     

    I would see if @domosupport on buzz or support@domo.com  has any suggestions. It's possible someone else on the board may have an idea on how to solve this in cardbuilder.

     

    If you want to build a solution outside card builder, that would be simpler in this case I think based on my testing.

    **Say 'Thanks' by clicking the thumbs up in the post that helped you.
    **Please mark the post that solves your problem as 'Accepted Solution'
  • IVG
    IVG Member

    I am trying now to do it in ETL - by first normalizing the date column with the "set column type" command and then grouping the days using "last value" command - the only problem is that once the time stamp is removed from the date it screws up the order since it is not selcting the last value.... so still trying to work this one out...

  • IVG
    IVG Member

    nope it is not working in ETL - can figure this out - I am so stumped - is there anyone out there who can help!? this is driving me nuts!

    Please!

  • PodiumMason
    PodiumMason Contributor

    @IVG 

     

    Do you know SQL and have access to dataflows? I can help you there if you do. 

     

    Otherwise, Domo support is always available to help. 

    **Say 'Thanks' by clicking the thumbs up in the post that helped you.
    **Please mark the post that solves your problem as 'Accepted Solution'
  • IVG
    IVG Member

    i have the access to the dataflows but I am a complete novice with SQL... I have been trying to figure out how to do it with SQL for last hour but unsuccessfully ? so any help will be much appreciated!

  • IVG
    IVG Member

    oh and domo support cut me off - i am using a free account and apprently there is a limit to how many times you can ask for help... 

  • PodiumMason
    PodiumMason Contributor

    @IVG

     

    So you'll want to create a dataflow in MySQL and do the following transform. When you have created the transform, output it with a Select * from transform_1 as a new dataset (name it whatever you like).

     

    Select
    Case when date(`datetime`) = Current_date() then `datetime` else Date_add(`Datetime`, Interval 1 Day) END as `date alignment`,
    SUM(Case when date(`DateTime`) = date_add(current_date, Interval -1 day) then `Value` else 0 end) as `Previous Value`,
    SUM(Case when date(`DateTime`) = CURRENT_DATE() then `Value` else 0 END) as `Current Value`
    from last_value_test
    group by `date alignment`
    order by `date alignment` DESC

     

     

    when you build the card, be sure to use the date alignment filter to filter down to 'YES' (as a beast mode):

     

    Case when `datetime` <= Now() then 'Yes' else 'No' END

     

     

    Let me know if this works for you!

    **Say 'Thanks' by clicking the thumbs up in the post that helped you.
    **Please mark the post that solves your problem as 'Accepted Solution'
  • IVG
    IVG Member

    again thanks for all the help but, a couple of follow up questions:

     

    So I managed to get the output following your instructions but I am getting the following result (see below)

    and that filter your suggested in beastmode gives "YES" to every line... so there is nothing to filter...

     

    Screen Shot 2018-02-23 at 5.46.35 PM.png

  • PodiumMason
    PodiumMason Contributor

    @IVG based on what you told me about your data, the filter was to make sure that when we aligned the dates that you wouldn't have any that are after the current moment in time. 

     

    For example if it was four o'clock you wouldn't see any yesterday results from five or six o'clock. (that don't have today's results yet). 

     

    **Say 'Thanks' by clicking the thumbs up in the post that helped you.
    **Please mark the post that solves your problem as 'Accepted Solution'
  • IVG
    IVG Member
    Answer ✓

    ok so I had a colleague write some SQL that made it work... so I will show you here just as a point of interest...

     

    SELECT (
    SELECT Value
    FROM dataset_1

    WHERE date = (
    SELECT min(date)
    FROM dataset_1

    WHERE date > DATE_SUB(NOW(), INTERVAL 24 HOUR)
    )
    ) as YESTERDAY,
    (
    SELECT Value
    FROM dataset_1
    WHERE date = (
    SELECT max(date)
    FROM dataset_1
    WHERE date > DATE_SUB(NOW(), INTERVAL 24 HOUR)
    )
    ) as TODAY

    FROM dataset_1
    LIMIT 1

     

    So this took care of all the issues - the result was that I got two values one from today and one from exactly 24hours ago in two separate columns - which made it perfect for using in a comparative guage!

  • [Deleted User]
    [Deleted User] Member

    Nice work @IVG on figuring this out!

    Kudos to @PodiumMason as well.

     

    Regards,

     

  • PodiumMason
    PodiumMason Contributor

    @IVG looks good, glad you got a solution that worked for your dataset!

     

    My sample dataset that I was using was definitely simpler and easier to work with it seems like. 

    **Say 'Thanks' by clicking the thumbs up in the post that helped you.
    **Please mark the post that solves your problem as 'Accepted Solution'

Categories