topic Single row log data in QlikView

Single row log data

Thu, 13 Feb 2014 08:40:11 GMT

Hi all,

Is it possible to load single row log data into QlikView ?

Example:

2AU891000-PC1 VL03N TRANSACTIONA CLIENT400 PH 2AU891000-PC2 VF03 TRANSACTIONB CLIENT400 ID 2AU891000-PC1 VA03 TRANSACTIONC CLIENT400 PH 2AU891000-PC1 VL03N TRANSACTIOND CLIENT400 PH

Note: new transaction will start with certain character, In this case is '2AU'.

Re: Single row log data

luis_pimentel — Thu, 13 Feb 2014 08:46:25 GMT

Yes, take a look to the SUBFIELD() function. Try something like:

LOAD

SUBFIELD(youfieldname,'2AU') AS yourfielname

RESIDENT yourtablename

that would create a new row every time '2AU' is found, with a single field.

If you want to load the data in more than one field, additional transformation will be needed.

Re: Single row log data

Thu, 13 Feb 2014 08:47:15 GMT

These file in category of Delimited file and data values are separated by spaces.

Re: Single row log data

hic — Thu, 13 Feb 2014 08:52:45 GMT

Yes, you can. Try

Load

Subfield(Replace(@1:n,'2AU','|'),'|') as Field

From LogFile.txt (fix, codepage is 1252);

The @1:n loads the entire line in a fixrecord file. The Replace() function inserts the '|' delimiter in the right places. Finally, the Subfield() function puts each column on its own line.

HIC

Re: Single row log data

Sokkorn — Thu, 13 Feb 2014 09:11:14 GMT

Hi Bunchong,

Here is another method

[Data]:

LOAD

Trim(SubField(tmpField,' ',1)) AS PC,

Trim(SubField(tmpField,' ',2)) AS tCode,

Trim(SubField(tmpField,' ',3)) AS Transaction,

Trim(SubField(tmpField,' ',4)) AS Client,

Trim(SubField(tmpField,' ',5)) AS Code

Where tmpField<>'2AU';

LOAD

'2AU' & SubField(@1:n,'2AU') As tmpField

FROM [Log.txt]

(fix, codepage is 1252);

Regards,

Sokkorn

Re: Single row log data

Thu, 13 Feb 2014 09:27:40 GMT

Thank you so much !