Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

gottsmann
New Contributor II

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

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.

1 Solution

Accepted Solutions
gottsmann
New Contributor II

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

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.

2 Replies

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

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;


talk is cheap, supply exceeds demand
gottsmann
New Contributor II

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

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.

Community Browser