Visokio website     Downloads     Video tutorials     KnowledgeBase  
Formulae: SUBSET(MAX) Extract data with latest date? - Visokio Forums
Formulae: SUBSET(MAX) Extract data with latest date?
  • Nemesis May 26, 2013 9:57PM
    How to extract data from database which has duplicates in column [A], duplicate data in column [B] and unique values (dates) corresponding to each row in column [C]. I want to extract the row which has latest date corresponding to unique value in column [A].

    [A]| [B] | [C]

    a | 1 | 10/12/2012
    a | 2 | 10/13/2012
    c | 1 | 10/14/2012
    d | 2 | 10/15/2012
    s | 3 | 10/16/2012
    d | 3 | 10/17/2012
    f | 212| 10/18/2012
    g | 4 | 10/19/2012
    e | 2 | 10/20/2012
    a | 1 | 10/21/2012
    e | 32 | 10/22/2012
    a | 32 | 10/23/2012
    b | 32 | 10/24/2012
    a | 3 | 10/25/2012
    b | 23 | 10/26/2012
    c | 2 | 10/27/2012
    c | 32 | 10/28/2012

    Like "a" having latest data of:

    "a 3 10/25/2012"

    and similarly for all other unique values from column [A]. Any suggestions please!!
  • 2 Comments
  •     tjbate May 27, 2013 6:54AM
    Nemisis - Omniscope's SUBSET functions, which are not found in spreadsheets, may be able to help with this. The syntax for Subset functions is demonstrated in the attached demo file. Place the Content View in Edit mode and click each of the formulae, selecting Edit Formula to see its syntax. Change the data in the file and see how the formulae are re-evaluated.

    In your example, you want:

    SUBSET_MAX[C],SUBSET([A])

    Which is read "For each unique value found in [A] take all rows where [C] is maximum and return the maximum [C] value (date).

    As is, this will return only the maximum date for each value in [A], so you need to build up the formula to also look at corresponding [B] values and perhaps what to do if there are more than one [B] values with the same maximum date in [C].

    Note that SUBSET functions return one value per cell per row, not the array of all row values for the evaluated subset. Reducing/eliminating the rows of no interest is done with filtering and Named Queries, which can take their cue(s) from the evaluated results of SUBSET functions like the one above.
  • Nemesis May 28, 2013 11:43AM
    thanx alot for quick reply.
    i had used another method to do it: first by sorting it (1st order by column A then 2nd order column C date) then de-duplicate to give the unique value in column A by latest date.
    My purpose was to publish the list for further use in excel.

    is there a way by which after publishing a data into a MS Access database another process could get the data from database and publish it after some modification like the one used above into excel.What i mean to say using a single click for the whole process instead of waiting for one process to get completed then moving onto another one (continuous flow).

Welcome!

It looks like you're new here. If you want to get involved, click one of these buttons!

Sign In Apply for Membership