topic Re: Getting the max value from pseudo-duplicated rows in QlikView

Getting the max value from pseudo-duplicated rows

Wed, 11 May 2016 10:08:29 GMT

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!

Re: Getting the max value from pseudo-duplicated rows

trdandamudi — Wed, 11 May 2016 15:50:00 GMT

May be as below:

Source_Table:

Load * Inline [

ID, Date

1, 16/06/2015

4, 12/01/2016

4, 25/02/2016

5, 11/11/2015

];

Right Join(Source_Table)

Load

ID,

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

Resident Source_Table

Group By ID;

Re: Getting the max value from pseudo-duplicated rows

sunny_talwar — Wed, 11 May 2016 17:18:45 GMT

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

4, 12/01/2016

4, 25/02/2016

5, 11/11/2015

];

FinalTable:

NoConcatenate

LOAD ID,

Date

Resident Source_Table

Where ID <> Previous(ID)

Order By ID, Date desc;

DROP Table Source_Table;

Re: Getting the max value from pseudo-duplicated rows

Thu, 12 May 2016 08:11:23 GMT

Thanks folks that's very helpful!