topic Re: Pre-Selection of single rows from multiple csv files in QlikView

Pre-Selection of single rows from multiple csv files

Thu, 02 Feb 2012 12:14:44 GMT

Hello,

One of our monitoring tools creates csv files in a folder hourly.
The structure of the file name is always the same "location" (e.g. Berlin, Munich) and "time" (hhmmss).
The files itself contain a header and some rows with data.

From all this files I need only the last from every location and from every hour. (below marked <==)
Berlin_091013.csv
Berlin_091114.csv
Berlin_091117.csv
Berlin_091219.csv <==
Munich_091005.csv
Munich_091506.csv
Munich_091607.csv
Munich_091708.csv <==
Munich_101205.csv
Munich_101206.csv
Munich_101307.csv
Munich_101308.csv <==

And from every of this files I need only the last record.

To exclude all the other files/records would be very helpful because there are about 50000 files in the folder and I need only about 400 rows.

But all my attempts failed.

Thanks in advance.

Re: Pre-Selection of single rows from multiple csv files

hic — Thu, 02 Feb 2012 12:42:36 GMT

for each vFile in FileList('*.csv')
Data1:
Load *,
Subfield(FileBaseName(),'_',1) as City,
Time#(Subfield(FileBaseName(),'_',2),'hhmmss') as Time
FROM [$(vFile)] (txt, codepage is 1252, embedded labels, delimiter is ',', msq);
next vFile

Left Join
Load
City,
Max(Time) as MaxTime
resident Data1
Group By City;

Data2:
Noconcatenate
Load * resident Data1
where Time=MaxTime;

Drop Table Data1;

Re: Pre-Selection of single rows from multiple csv files

hic — Thu, 02 Feb 2012 12:46:25 GMT

I just saw a simpler solution...

for each vFile in FileList('*.csv')
Data1:
Load *,
Subfield(FileBaseName(),'_',1) as City,
Time#(Subfield(FileBaseName(),'_',2),'hhmmss') as Time
FROM [$(vFile)] (txt, codepage is 1252, embedded labels, delimiter is ',', msq);
next vFile

Inner Join
Load
City,
Max(Time) as Time
resident Data1
Group By City;

Pre-Selection of single rows from multiple csv files

Sat, 04 Feb 2012 10:49:29 GMT

Thanks Henric, your solution works very well.

Now to my second question.
How can I work with the results?

Let's assume I got following result:
Munich 20120103 5
Munich 20120104 7
Munich 20120105 6
Berlin 20120103 15
Berlin 20120104 17
Berlin 20120105 16

It would be helpful to create charts with expressions related to the resuls not to all data:
Munich max: 7
Munich avg: 6
Munich min: 5
Berlin max: 17
Berlin avg: 16
Berlin min: 15

20120103 max: 15
20120103 avg: 10
20120103 min: 5
20120104 max: 17
20120104 avg: 12
20120104 min: 7

Thanks.

Re: Pre-Selection of single rows from multiple csv files

Sat, 04 Feb 2012 10:50:09 GMT

Thanks Henric, your solution works very well.

Now to my second question.
How can I work with the results?

Let's assume I got following result:
Munich 20120103 5
Munich 20120104 7
Munich 20120105 6
Berlin 20120103 15
Berlin 20120104 17
Berlin 20120105 16

It would be helpful to create charts with expressions related to the resuls not to all data:
Munich max: 7
Munich avg: 6
Munich min: 5
Berlin max: 17
Berlin avg: 16
Berlin min: 15

20120103 max: 15
20120103 avg: 10
20120103 min: 5
20120104 max: 17
20120104 avg: 12
20120104 min: 7

Thanks.

Pre-Selection of single rows from multiple csv files

hic — Sat, 04 Feb 2012 14:52:02 GMT

This should be straightforward: Create a chart (right-click on sheet - new sheet object) where you use one dimension, e.g. City, and three expressions, Max(Data), Avg(Data) and Min(Data). I would start with a bar chart or a pivot table and change it later when I know that I have the numbers I look for.

You could have several dimensions in one chart, but since it is easier to read a chart with just one dimension, I would probably make several charts: One with City as dimension and a second with Date/Time as dimension.