Discussion Board for collaboration on QlikView Scripting.
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!
Try to set the delimited as space so that it can identify the fields and use Cross Table
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,
How come all the fields are not available in all the rows, is the way coming from data source.,?
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.
Try this script
SubField(DataType, '=', 1) AS FieldType,
SubField(DataType, '=', 2) AS FieldValue;
RecNo() AS RecordNum,
SubField(Data, ' ') AS DataType
date=2015-05-04 time=23:59:58 logid=0000000013 type=traffic level=notice vd=root srcip=192.168.1.215];
DROP TABLE Temp;
I think to use subfield() is a practiable solution:
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
// your example-record saved as txt-file
(fix, codepage is 1252);