Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Dynamic data source structure

Hi Guys,

I am facing some difficulty to load my flat file into Qlikview.

Please find below as the structure of the flat file.

date=2015-05-04 time=23:59:58 logid=0000000013 type=traffic subtype=forward level=notice vd=root srcip=192.168.1.215

date=2015-05-05 time=23:59:58 logid=0000000013 type=traffic subtype=forward level=notice srcip=192.168.1.216

date=2015-05-04 time=23:59:58 logid=0000000013 type=traffic level=notice vd=root srcip=192.168.1.215


As you can see, the structure for each record could be different. The column name is left side of the equal sign and the data is on right side.

May i know how to handle such unstructured data source?


Thanks in advance!


Regards,

Thai

6 Replies
vardhancse
Valued Contributor II

Re: Dynamic data source structure

Hi,

Try to set the delimited as space so that it can identify the fields and use Cross Table

Not applicable

Re: Dynamic data source structure

Hi Sasi,

I have set the delimiter as space, but the problem is each transaction will have different numbers of column.

All the column name is inside the transaction itself. The file does not comes with header.

Thanks and Regards,

Thai

vardhancse
Valued Contributor II

Re: Dynamic data source structure

Hi ,

How come all the fields are not available in all the rows, is the way coming from data source.,?

Not applicable

Re: Dynamic data source structure

Hi Sasi,

Yes, this is coming directly from data source. Those column without data will not be populated into the flat file.

Therefore certain records will missing some columns.

Regards,

Thai

MVP
MVP

Re: Dynamic data source structure

Hi,

Try this script

Temp:

LOAD

RecordNum,

SubField(DataType, '=', 1) AS FieldType,

SubField(DataType, '=', 2) AS FieldValue;

LOAD

RecNo() AS RecordNum,

SubField(Data, ' ') AS DataType

INLINE [

Data

date=2015-05-04 time=23:59:58 logid=0000000013 type=traffic subtype=forward level=notice vd=root srcip=192.168.1.215

date=2015-05-05 time=23:59:58 logid=0000000013 type=traffic subtype=forward level=notice srcip=192.168.1.216

date=2015-05-04 time=23:59:58 logid=0000000013 type=traffic level=notice vd=root srcip=192.168.1.215];

Data:

Generic

LOAD

RecordNum,

FieldType,

FieldValue

RESIDENT Temp;

DROP TABLE Temp;

Regards,

Jagan.

Re: Dynamic data source structure

I think to use subfield() is a practiable solution:

table:

Load subfield(RecordValue, '=', 1) as Categorie, subfield(RecordValue, '=', 2) as Value, rowno() as RowNo, RecNo;

LOAD subfield(Record, ' ') as RecordValue, RecNo;

LOAD @1:n as Record, recno() as RecNo

FROM

// your example-record saved as txt-file

(fix, codepage is 1252);

- Marcus

Community Browser