topic Re: Load highest value for Key in QlikView

Load highest value for Key

Anonymous — Thu, 04 Feb 2016 07:48:54 GMT

Hello,

I think there must be a pretty simple solution but I just cant figure it out.

I got a key, which appears multiple times in one table with several time-stamps NOT sorted.

What I want is a table with each key appearing once, distinct, and the latest time-stamp to it from table A.

Table A:

Key, Date, Time

10241, 20160204, 94525

10241, 20160204, 83858

10241, 20160204, 101245

10241, 20160204, 73248

10241, 20160204, 165847

10241, 20160204, 124151

10247, 20160204, 45712

10247, 20160204, 54623

10247, 20160204, 112154

10247, 20160204, 73248

10258, 20160204, 73248

Any input is appreciated!!

Thx,

Lucas

Re: Load highest value for Key

Anonymous — Thu, 04 Feb 2016 09:16:30 GMT

found the answer myself

Check which value is the Min and Max in Load Script

Re: Load highest value for Key

sunny_talwar — Thu, 04 Feb 2016 17:22:21 GMT

May be this:

[Table A]:

LOAD * Inline [

Key, Date, Time

10241, 20160204, 94525

10241, 20160204, 83858

10241, 20160204, 101245

10241, 20160204, 73248

10241, 20160204, 165847

10241, 20160204, 124151

10247, 20160204, 45712

10247, 20160204, 54623

10247, 20160204, 112154

10247, 20160204, 73248

10258, 20160204, 73248

];

Right Join ([Table A])

LOAD Key,

Date,

Max(Time) as Time

Resident [Table A]

Group By Key, Date;

Re: Load highest value for Key

Gysbert_Wassenaar — Thu, 04 Feb 2016 17:23:37 GMT

Use the Max function:

LOAD

Key, Date, Max(Time) as Time

FROM

MySource

GROUP BY

Key, Date

;