topic Re: Getting the latest records for each day based on a Counter/Timestamp in QlikView

Getting the latest records for each day based on a Counter/Timestamp

Fri, 12 Feb 2016 13:00:06 GMT

Hi,

I'm having a table like this:

I want to create a new table in the script, which only contains the records for the highest Counter pr Date pr Item.

The result should be:

sunny_talwar — Fri, 12 Feb 2016 17:47:22 GMT

Try this:

Set DateFormat='DD.MM.YYYY';

T1:

LOAD Item, Value, Date(Day) as Date, Counter

INLINE [

Item, Value, Day, Counter

1, 5, 01.02.2016, 100

1, 6, 01.02.2016, 101

2, 5, 01.02.2016, 102

1, 7, 01.02.2016, 103

2, 4, 01.02.2016, 104

1, 6, 02.02.2016, 105

1, 4, 02.02.2016, 106

];

Right Join(T1)

LOAD Item,

Date,

Max(Counter) as Counter

Resident T1

Group By Item, Date;

maxgro — Fri, 12 Feb 2016 22:53:29 GMT

Set DateFormat='DD.MM.YYYY';

T1:

LOAD Item, Value, Date(Day) as Date, Counter

INLINE [

Item, Value, Day, Counter

1, 5, 01.02.2016, 100

1, 6, 01.02.2016, 101

2, 5, 01.02.2016, 102

1, 7, 01.02.2016, 103

2, 4, 01.02.2016, 104

1, 6, 02.02.2016, 105

1, 4, 02.02.2016, 106

];

NoConcatenate LOAD * Resident T1

Where Item & '|' & Date <> Peek('Item') & '|' & Peek('Date')

Order By Item, Date, Counter desc;

DROP Table T1;