topic Re: Dynamic data source structure in QlikView

Dynamic data source structure

Mon, 25 May 2015 02:55:04 GMT

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

Re: Dynamic data source structure

vardhancse — Mon, 25 May 2015 03:27:11 GMT

Hi,

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

Re: Dynamic data source structure

Mon, 25 May 2015 03:40:01 GMT

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

Re: Dynamic data source structure

vardhancse — Mon, 25 May 2015 03:45:51 GMT

Hi ,

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

Re: Dynamic data source structure

Mon, 25 May 2015 05:28:03 GMT

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

Re: Dynamic data source structure

jagan — Mon, 25 May 2015 05:37:18 GMT

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

marcus_sommer — Mon, 25 May 2015 05:58:04 GMT

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