Find Most Recent Date

mbelmont
mbelmont Contributor
in Magic ETL

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!

Best Answer

  • AS
    AS 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

  • AS
    AS Coach

    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?

     

    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"
  • mbelmont
    mbelmont Contributor

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

  • AS
    AS 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"
  • kshah008
    kshah008 Contributor

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

  • mbelmont
    mbelmont Contributor

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

  • rado98
    rado98 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

  • BSovers
    BSovers Member

    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!

Categories