topic Re: Loading only max values in QlikView

Loading only max values

Tue, 23 Dec 2014 15:03:41 GMT

Hi guys,

I am loading an SQL table with values for Date, Type and Amount. Sometimes I have multiple values for the same Date and Type, but with different amounts. For each Date and Type combo, I'm looking to load only those records that have the Max amount.

Date Type Amount

x a 10

y a 12

z a 8

z a 15

So in this table I only want z, a, 15 to load. Any way I can simply do this by altering my Load script maybe?

Re: Loading only max values

jpapador — Tue, 23 Dec 2014 15:06:47 GMT

LOAD

Date,

Type,

Max(Amount) as MaxAmount

Resident Temp1

Group By Date, Type;

Re: Loading only max values

its_anandrjs — Tue, 23 Dec 2014 15:15:37 GMT

Hi,

You can try this also

Raw:
LOAD * INLINE [
Date, Type, Amount
x, a, 10
y, a, 12
z, a, 8
z, a, 15
];

LOAD
Max(Amount) as Amount
Resident Raw;

Left Join
LOAD * Resident Raw;

DROP Table Raw;

Regards

Anand

Re: Loading only max values

Gysbert_Wassenaar — Tue, 23 Dec 2014 15:22:39 GMT

MyTable:

SQL SELECT Date, Type, Amount

FROM ...mytable...;

right join (MyTable)

LOAD Type, max(Amount) as Amount;

RESIDENT MyTable

GROUP BY Type;

Re: Loading only max values

anbu1984 — Tue, 23 Dec 2014 16:31:49 GMT

LOAD

Date,

Type,

Firstsortedvalue(Amount,-Amount) as MaxAmount

Group By Date, Type;

Sql Select Date,Type,Amount From Table;

LOAD

Date,

Type,

Max(Amount) as MaxAmount

Group By Date, Type;

Sql Select Date,Type,Amount From Table;

Re: Loading only max values

Wed, 24 Dec 2014 09:29:47 GMT

Thanks! I couldn't get it to work at first but when I took the max of a new variable, date and type combined, the right join worked like a charm