topic Re: How to load row´s with highest/lowest fieldvalue in QlikView

How to load row´s with highest/lowest fieldvalue

joweis4711 — Mon, 01 Dec 2014 14:26:55 GMT

Hi!

I would like to load only these rows out of a .qvd-file where the field "REQ UTC TSTAMP" has the highest value group by "FAL".

(In the original .qvd-file there are 450 more Fields!)

Many thanks for a simple solution.

Re: How to load row´s with highest/lowest fieldvalue

its_anandrjs — Mon, 01 Dec 2014 14:37:27 GMT

Hi,

In the load script create the another table like

Load

FAL,

Max("REQ UTC TSTAMP") as [Max REQ UTC TSTAMP]

Resident SourceTable

Group FAL;

Regards

Anand

Re: How to load row´s with highest/lowest fieldvalue

hic — Mon, 01 Dec 2014 14:37:29 GMT

Data1:

Load * From Source.qvd (qvd);

Left Join

Load FAL, Max([REQ UTC TSTAMP]) as MaxTime Resident Data1 Group By FAL;

Data2:

Load * Resident Data1 Where [REQ UTC TSTAMP] = MaxTime;

Drop Table Data1;

//HIC

Re: How to load row´s with highest/lowest fieldvalue

Peter_Cammaert — Mon, 01 Dec 2014 14:47:56 GMT

Decide on a primary key for every record (primary key is unique for every record)
Use a GROUP BY LOAD to get the Max records. Load the corresponding field values by using firstsortedvalmue() function. Load only the minimal set of fields, e.g. if your prilmary key is composed of 5 fields, you want to load FAL, [REQ UTS TSTAMP] and all individual fields that make up your primary key
LEFT JOIN the other fields to this initial Max table.

You may want to load the QVD in memory first, as a JOIN is resource hungry.

For example, imagine that I have a primary key composed of three fields: FIELD1, FIELD2 and FIELD3

RawData:

LOAD * FROM InputFile.QVD (qvd); // Very fast load

MaxTable:

NOCONCATENATE

LOAD FAL,

Max([REQ UTS TSTAMP]) AS [MAX REQ UTS TSTAMP],

FirstSortedValue(FIELD1, -[REQ UTS TSTAMP]) AS FIELD1,

FirstSortedValue(FIELD2, -[REQ UTS TSTAMP]) AS FIELD2,

FirstSortedValue(FIELD3, -[REQ UTS TSTAMP]) AS FIELD3,

RESIDENT RawData

GROUP BY FAL;

LEFT JOIN (MaxTable)

LOAD * RESIDENT RawData;

DROP Table RawData;

We used * in all places where you run the risk of having to specify hundreds of additional field names.

Best,

Peter