Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hi,
Try to set the delimited as space so that it can identify the fields and use Cross Table
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
Hi ,
How come all the fields are not available in all the rows, is the way coming from data source.,?
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
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.
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
(fix, codepage is 1252);
- Marcus