topic subfield unequal string length in QlikView

subfield unequal string length

Michiel_QV_Fan — Thu, 14 Apr 2011 12:23:27 GMT

Hi all,

I have a log file from SAP load and want to extract the number of lines fetched form the log file (txt).

I tried these functions or combinations:

if(@26:n like '*lines fetched*', 'lines fetched', '') as find_lines,
subfield(subfield(@26:n, ','), 'lines fetched') as text_parts,
subfield(@26:n, 'lines fetched') as bla,
textbetween(@26:n, ',' , 'lines fetched') as lines,
substringcount(@26:n, ',') as [, count],
subfield(@26:n, ',', substringcount(@26:n, ',')) as lines?,
index(@26:n, ',') as index,

none delivered the last part of the log file sentence which contains "<number> lines fetched"

The text looks like this:

4 fields found: DIMID, SID_0CHNGID, SID_0RECORDTP, SID_0REQUID, 36 lines fetched

or like this

1 fields found: Dimensionloaded, 0 lines fetched

or .....

How can I extract the last part of each sentence? Your help is much appreciated.

subfield unequal string length

Miguel_Angel_Baeyens — Thu, 14 Apr 2011 12:36:57 GMT

Hello,

The following should work

Lines:LOAD KeepChar(Right(@1:n, Len(@1:n) - Index(@1:n, ', ', -1) +1), '0123456789') AS LinesFROM File.csv(fix, codepage is 1252);

Hope that helps.

subfield unequal string length

rbecher — Thu, 14 Apr 2011 12:46:36 GMT

You could use a stacked LOAD with a where clause:

LOAD * WHERE sfield like '*lines fetched";
LOAD ... subfield(subfield(@26:n, ',') as sfield ...;

- Ralf

subfield unequal string length

Michiel_QV_Fan — Thu, 14 Apr 2011 12:58:09 GMT

Works great. Thanks!

subfield unequal string length

Michiel_QV_Fan — Thu, 14 Apr 2011 13:08:09 GMT

Ralf, this statement doesn't work.

Subfield needs more parameters is the QV message.

my final row is grey despite that the subfield coding has the correct colors.

syntax check gives an error on 'as sfield'

the code:

copa2:
load * where beschrijving like '*lines fetched';
LOAD @1:10 as datum,
@11:19 as tijd,
@20:20 as :,
@21:25 as regel,
@26:n as beschrijving,
subfield(subfield(@26:n, ',') as sfield,
FileName() as logbestand
FROM

error: Error in expression: ')' expected

added )

error: Error in expression: SubField takes 2-3 parameters

subfield unequal string length

rbecher — Thu, 14 Apr 2011 13:31:09 GMT

Sorry, one subfield too much!

LOAD * WHERE sfield like '*lines fetched";
LOAD ... subfield(@26:n, ',') as sfield ...;

- Ralf