topic Re: How to get the minimum or maximum value for every distinct row reading from multiple files? in QlikView

How to get the minimum or maximum value for every distinct row reading from multiple files?

GeorgeQV — Fri, 26 Feb 2016 09:44:18 GMT

Dear all,

let's say I have three txt-files like this:

List1:

ID Value

1 100

2 200

3 300

List2:

ID Value

1 80

2 250

List3:

ID Value

1 75

2 150

1 12

How can I read those files sequentially and store the minimum or maximum value over all records for every distinct ID? No matter in which order I read the lists, the result should be:

ID Value

1 12

2 150

3 300

Thank you in advance.

Re: How to get the minimum or maximum value for every distinct row reading from multiple files?

Gysbert_Wassenaar — Fri, 26 Feb 2016 10:52:23 GMT

Temp:

LOAD

ID, min(Value) as V1

FROM

List1

GROUP BY

ID;

JOIN (Temp)

LOAD

ID, min(Value) as V2

FROM

List2

GROUP BY

ID;

JOIN (Temp)

LOAD

ID, min(Value) as V3

FROM

List3

GROUP BY

ID;

Result:

LOAD

ID, Rangemin(V1,V2,V3) as MinValue

RESIDENT

Temp;

DROP TABLE Temp;

Re: How to get the minimum or maximum value for every distinct row reading from multiple files?

GeorgeQV — Fri, 26 Feb 2016 14:09:25 GMT

Dear Gysbert,

thank you for your answer. It is great when there are really only three files, but actually I have hundreds of them, three was just an example. I have a solution now:

When reading in the files, use the wildcard in the LOAD statement

Table:
LOAD ID, Value
FROM ..\List*.txt
(txt, codepage is 1252, embedded labels, delimiter is ';', no quotes);

All my lists are then sequentially read and their rows added to the table.

After that you can create another table for aggregation:

MinMaxValue:
LOAD min(Value) as MinValue,
     max(Value) as MaxValue
Resident Table group by ID;

The group by statement is important. Finally you have a table with all aggregations grouped by the ID.