Skip to content

    • Beast Mode

    Beast Mode

    LAG / LEAD function misses the last iteration

    Member
    in Beast Mode

    Hi experts!

    I'm using the lead / lag function in beast mode to find out the last row from each group of index rows simply by comparing the value of primary key of next/previous. It works accurately except it misses the last / first group (last when using lead and first when using lag).

    Here's my beast mode code:

    image.png


    And, here's my data table and result of the calculated field...

    image.png


    If you see it correctly punches '1' for the group Canada, but misses for US. If I use the lag function, it punches value for US but misses for Canada.

    One way I could think of is to punch '1' by default for the last row, but I couldn't found the way to use WHEN and identify the last row - if you could help me with that, it would be great.

    ---------------------------------------

    Currently, I'm using a sloppy workaround of using both 😋 (see below) - it works but I want to do it much cleaner way.

    Current workaround:

    image.png image.png


    Best Answer

    Answers

    • Member

      Also, if there's only one group, then lead/lag doesn't work at all!!

      So, in that case, I just want to punch '1' for the last row. Is there a way to use CASE..WHEN..END to punch a value for the last row? something like...

      CASE

      WHEN CURRENT_ROW() = LAST_ROW() THEN 1

      END

      ==================================================================

      Using MAX & FIXED BY works in general, see below...

      image.png image.png


      ... but when I apply filter on that calculated field to show only latest row, it picks the first row with blank version irrespectively. Ideally it should have filtered the row with version "2022-11-08-0014", however, it picks the row with blank version irrespectively. I even tried to sort the versions with descending order but doesn't work...

      image.png


    • Member
      Answer ✓

      Hi - rank() and count() did the work!

      image.png


    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Quick Links

    Categories