Getting the max value from pseudo-duplicated rows

[#1]

Howdy, I'm new to qlikview and am currently unsure of the best way to approach this problem,

I have a table with data like so...

Table:

ID,          Date

1             16/06/2015

2             -

3             -

4             12/01/2016

4             25/02/2016

5             11/11/2015

5             -

(data was generated by splitting subfields and using an applymap)

I would like to be able to get the max value (most recent date) for each ID.

Table:

ID,          Date

1             16/06/2015

2             -

3             -

4             25/02/2016

5             11/11/2015

Is there an efficient way to do this in qlikview? I'm more of an OO developer really and can think of potentially looping through the table, checking if the previous ID is the same and using flags and the peek function to determine which rows to load but am aware that there's probably a much more efficient way to do this. Any help would be greatly appreciated!

[trdandamudi]

May be as below:

Source_Table:

Load * Inline [

ID,          Date

1,             16/06/2015

2,           

3,           

4,             12/01/2016

4,             25/02/2016

5,             11/11/2015

5,           

];

Right Join(Source_Table)

Load

ID,

Max(Date(Date), 'DD/MM/YYYY') as Date

Resident Source_Table

Group By ID;

[sunny_talwar]

Another option can be this:

Source_Table:

LOAD ID,

  If(Len(Trim(Date)) > 0, Date) as Date;

Load * Inline [

ID,      Date

1,      16/06/2015

2,           

3,           

4,      12/01/2016

4,      25/02/2016

5,      11/11/2015

5,           

];

FinalTable:

NoConcatenate

LOAD ID,

  Date

Resident Source_Table

Where ID <> Previous(ID)

Order By ID, Date desc;

DROP Table Source_Table;

[#4]

Thanks folks that's very helpful!