Magic ETL

Magic ETL

Find Most Recent Date

Hey all, I am trying to find a way to locate the most recent date in a table based on rows of multiple columns.  In other words, given a single case with variables X1, X2 and X3 I want the beastmode to find the latest date.  So if column 1 was 1/1/2015, column 2 was 2/2/2014 and column 3 was 5/1/2016 then the calculation would return 5/1/2016.  

 

Thanks all!

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

Best Answer

  • Coach
    Answer ✓

    Oh yeah, that's a different story.  Two dozen columns would be a nightmare with what I suggested.  

     

    The SQL function GREATEST() would probably work well in this case but BeastMode doesn't have that function that I'm aware.

     

    What's your use case, just finding a max value from an entire dataset? (try using a SQL dataflow with GREATEST)

    Or max value from any number of dimensional breakdowns that might be chosen with a card filter? (not sure what to do there but you'd need a BeastMode)

    Aaron
    MajorDomo @ Merit Medical

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

Answers

  • I'd suggest doing something like max(max(x1),max(x2),max(x3)), but I don't think beastmode max functions will allow that.

     

    Can you do a case statement with boolean?

     

    1. case
      when max(x1) >= max(x2) and max(x1) >= max(x3) then max(x1)
      when max(x2) >= max(x1) and max(x2) >= max(x3) then max(x2)
      when max(x3) >= max(x1) and max(x3) >= max(x2) then max(x3)
      end

    Seemed to work for me when I tested it.

    Aaron
    MajorDomo @ Merit Medical

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

    Yes, however I was hoping for a more elegant solution because I have about two dozen columns that I need to check against.  

  • Coach
    Answer ✓

    Oh yeah, that's a different story.  Two dozen columns would be a nightmare with what I suggested.  

     

    The SQL function GREATEST() would probably work well in this case but BeastMode doesn't have that function that I'm aware.

     

    What's your use case, just finding a max value from an entire dataset? (try using a SQL dataflow with GREATEST)

    Or max value from any number of dimensional breakdowns that might be chosen with a card filter? (not sure what to do there but you'd need a BeastMode)

    Aaron
    MajorDomo @ Merit Medical

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

    @mbelmont, tagging you to check out AS's reply. 

  • Contributor

    Yes, I am simply trying to find the most recent date from a table.  I think a SQL dataflow would work.  Thanks.

  • Contributor

    For the non-SQL people like me....

    Collapsing all date columns into a single column using ETL, then applying a MAX in ETL or beastmode should also do the trick

  • I needed to do this with 16 source columns, with the added "fun", that

    case when Col x > Col Y then Col x else Col y end —- had to be extended to:

    Case when col x is null then col y when Col y is null then Col x when Col x > Col Y then Col x else Col y end…

    I "handled" it with a total of 15 calculated fields max_ab, max_cd… max_op, max_abcd, … max_mnop, … max_a_h, max_i_p, then finally max_a_p.

    This would have been SO much easier with a GREATEST function! As long as it correctly handled null values!

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