Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
Specialist III
Specialist III

Hi,

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

Not applicable
Author

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
Specialist III
Specialist III

Hi ,

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

Not applicable
Author

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

jagan
Luminary Alumni
Luminary Alumni

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.

marcus_sommer

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