How to load row´s with highest/lowest fieldvalue

[joweis4711]

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.

[its_anandrjs]

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

[hic · Accepted solution]

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

[Peter_Cammaert]

• 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