Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Max Value Within an Array

Hello,


I have a table with a set of unique IDs, shared IDs, and Dates.  When loading the data, I'm attempting to add a calculated column which lists the maximum date associated with the shared IDs for each unique ID.  I've been able to determine these values in excel using an array formula, but I'm at a loss as to how I should proceed with loading and modifying this data using QlikView.  There may be any number of unique IDs associated with the same Shared ID.

For example, something that inputs the 3 leftmost columns and will be able to output the maximum date.

Unique IDShared IDDate*Maximum Date*
1A1/1/20171/1/2017
2A12/12/20161/1/2017
3B2/2/20172/2/2017
4B11/11/20162/2/2017

Thank you in advance for your help!

1 Solution

Accepted Solutions
sunny_talwar

Another way to do this:

Table:

LOAD * INLINE [

    Unique ID, Shared ID, Date

    1, A, 1/1/2017

    2, A, 12/12/2016

    3, B, 2/2/2017

    4, B, 11/11/2016

];

FinalTable:

LOAD *,

  If([Shared ID] = Previous([Shared ID]), Peek('MaxDate'), Date) as MaxDate

Resident Table

Order By [Shared ID], Date desc;

DROP Table Table;

View solution in original post

5 Replies
lironbaram
Partner
Partner

hi

assuming your source table has three fields try this

Table

load [Unique ID],[shared ID],[Date]

FROM your Table

left join (Table )

load [shared ID],

        max([Date]) AS Maximum_Date

resident Table

sunny_talwar

Another way to do this:

Table:

LOAD * INLINE [

    Unique ID, Shared ID, Date

    1, A, 1/1/2017

    2, A, 12/12/2016

    3, B, 2/2/2017

    4, B, 11/11/2016

];

FinalTable:

LOAD *,

  If([Shared ID] = Previous([Shared ID]), Peek('MaxDate'), Date) as MaxDate

Resident Table

Order By [Shared ID], Date desc;

DROP Table Table;

Not applicable
Author

I've tried this solution a number of ways but it says 'Invalid expression' highlighting from left join onward.  The other answer did work however.  Thank you

Not applicable
Author

Thank you this worked perfectly!

sunny_talwar

Some minor corrections

Table:

LOAD [Unique ID],

          [shared ID],

          [Date]

FROM yourTable;

Left Join (Table)

LOAD [shared ID],

          Max([Date]) as Maximum_Date

Resident Table

Group By [shared ID];