topic Re: Find Number of Records Processed in QlikView

Find Number of Records Processed

bhelms — Mon, 30 Sep 2013 20:44:56 GMT

I am trying to write an application that will show the number of records processed for each application when loaded through the QEMC. Most of the information I am looking for is in two log files, in the distribution service folder. The DocumentLog.txt and the TaskLog.txt. I have written the below script and it is working fine (although messy) when only looking at one subfolder (task).

LOAD @1 as Date,

min(@2) as Started_Time,

max(@2) as Ended_Time,

Sum(@5) as Records_Processed

FROM [..\DocumentLog.txt] (txt, utf8, no labels, delimiter is spaces, msq)

Where @6 like '*lines*'

Group by @1;

join

LOAD trim(Mid((trim(Mid(@3,(index( @3, '\', -1 )+1), 200))),1,(index((trim(Mid(@3,(index( @3, '\', -1 )+1), 200))), '"', -1 )-1))) as QVW_Name1,

trim(Mid((trim(Mid(@3,(index( @3, '\', -1 )+1), 200))),1,(index((trim(Mid(@3,(index( @3, '\', -1 )+1), 200))), '.', -1 )-1))) as QVW_Name2

FROM [..\TaskLog.txt] (txt, utf8, no labels, delimiter is '\t', msq)

Where @3 like 'Opening "*';

The problem comes in when trying to roll this out to all jobs since it has to loop through all of the subfolders and doesn't seem to work. Does anyone have an easier way to do this?

Re: Find Number of Records Processed

spividori — Mon, 30 Sep 2013 21:04:29 GMT

Hi.

Could you say how are structured the subfolders you need to access?

Regards.

Re: Find Number of Records Processed

bhelms — Mon, 30 Sep 2013 21:12:53 GMT

The folders I am accessing are the standard QVPR folders for QlikView. Each task has its own folder, and within that folder are these two documents. I need a way to merge these two documents, since they each have seperate information that I need but are related to eachother, for each folder

Re: Find Number of Records Processed

spividori — Mon, 30 Sep 2013 21:36:00 GMT

Hi.

I read txt files are in a subfolder and do as follows:

FilePath='*.txt';
for each File in filelist ('$(FilePath)')
SET sFile = '$(File)';
Directory;
Liquidacion:
LOAD @1:3 as LegajoLiq,
@4:36 as Apellido_y_nombre,
@37:48 as Haberes,
FROM
[$(sFile)]
(fix, codepage is 1252);
next File

Hope this helps.

Regards.

Re: Find Number of Records Processed

rwunderlich — Mon, 30 Sep 2013 23:13:14 GMT

You can loop through the sub directories using the FOR EACH DIRLIST script statement found in the ref guide.

-Rob

Re: Find Number of Records Processed

bhelms — Tue, 01 Oct 2013 17:01:10 GMT

After a bit more testing, and attempting to make this simpler, I came up with the following, which appears to be working. Gives a big synthethic key though, which I am not sure of any way around. Does each loop create its own table in Qlikview? Any way to keep this all as one table and keep loading into it rather than creating a new table for every loop?

sub Test (Root)

for each Dir in DirList(Root & '\*')
Run_Times:
LOAD @1 as Date,
     min(@2) as Started_Time,
     max(@2) as Ended_Time,
     Sum(@5) as Records_Processed
FROM [$(Dir)\DocumentLog.txt] (txt, utf8, no labels, delimiter is spaces, msq) Where @6 like '*lines*' Group by @1;
join
LOAD @3 as QVW_Name1,
FROM [$(Dir)\TaskLog.txt] (txt, utf8, no labels, delimiter is '\t', msq)
Where @3 like 'Opening "*';

next Dir

End Sub

Call Test('F:\Data\Test');

Re: Find Number of Records Processed

spividori — Tue, 01 Oct 2013 18:50:45 GMT

Hi.

Test with the following:

sub Test (Root)

for each Dir in DirList(Root & '\*')
Run_TimesAux:
LOAD @1 as DateAux,
     min(@2) as Started_TimeAux,
     max(@2) as Ended_TimeAux,
     Sum(@5) as Records_ProcessedAux
FROM [$(Dir)\DocumentLog.txt] (txt, utf8, no labels, delimiter is spaces, msq) Where @6 like '*lines*' Group by @1;
join(Run_TimesAux)
LOAD @3 as QVW_Name1Aux,
FROM [$(Dir)\TaskLog.txt] (txt, utf8, no labels, delimiter is '\t', msq)
Where @3 like 'Opening "*';

Run_Times:

LOAD DateAux as Date,

Started_TimeAux as Started_Time,

Ended_TimeAux as Ended_Time,

Records_ProcessedAux as Records_Processed,

QVW_Name1Aux as QVW_Name1

Resident Run_TimesAux:;

drop table Run_TimesAux;

next Dir

End Sub

Call Test('F:\Data\Test');

Regards.

Re: Find Number of Records Processed

rwunderlich — Tue, 01 Oct 2013 22:41:13 GMT

If you read the *.qvw.log files form the Source directories, you can get the QVW Name from the logfile name. That only gives you one day at a time, which may not be what you're after.

-Rob