topic Re: Text files. in QlikView

Text files.

raadwiptec — Fri, 02 Oct 2015 13:40:40 GMT

Hi all,

I have text data file as below

its only a sample.

id=transit secs="2010-03-07 11:36:44" route=6 mk=10 username=sam type=on schedule

id=peek secs="2010-03-07 11:36:44" route=9 mk=10 username=sam type=on schedule

id=dest secs="2010-03-07 11:36:44" route=10 mk=10 username=sam type=on schedule

id= arr secs="2010-03-07 11:36:44" route=12 mk=10 username=sam type=on schedule

but the actual file is around 15MB with the similar kind of delimited space structure.

so here I tried to do a cross table but it does not make the data structure good enough

so if Iam able to take just the Id part i.e without =transit .similarly secs part excluding timespamp..

any suggestions here

Re: Text files.

Gysbert_Wassenaar — Fri, 02 Oct 2015 15:57:33 GMT

Perhaps something like this:

LOAD

TextBetween(@1,'id=',' secs') as id,

TextBetween(@1,'secs=', ' route') as secs,

TextBetween(@1,'route=',' mk') as route,

TextBetween(@1,'mk=',' username') as mk,

TextBetween(@1,'username=',' type') as username,

SubField(@1,'=',-1) as schedule

FROM ...

Re: Text files.

raadwiptec — Mon, 05 Oct 2015 06:53:10 GMT

Hi Gysbert,

I don't get any results ..it is empty.

the source is a flat file and data is as below

for example:-

id=transit secs="2010-03-07 11:36:44" route=6 mk=10 username=sam type=on schedule. I am trying split each of them as a column

for ex

id =transit -- so id is a column here and 'transit' is the value

secs="2010-03-07 11:36:44" - so here I want to make 'secs' as the column and 2010-03-07 11:36:44" this is the value and this value may change accordingly with different timestamps and dates

Re: Text files.

jonathandienst — Mon, 05 Oct 2015 13:24:22 GMT

I think you need to use the two parameter subfield() and a generic load. But you will also need to prevent the subfield splitting the date and time and the 'on schedule' value. The generic load will also need some sort of row ID. Something like:

// load the source with some mangling to handle the edge cases

Raw:

// replace ' ' inside quotes with '_'

LOAD 'row=' & RowNo() & ' ' & SubField(Fields, '"', 1) & Replace(SubField(Fields, '"', 2), ' ', '_') & SubField(Fields,'"', 3) As Fields

;

// replace the ' ' in 'on schedule'

LOAD Replace(Fields, ' schedule', '_schedule') As Fields

;

LOAD Fields Inline

[

Fields

id=transit secs="2010-03-07 11:36:44" route=6 mk=10 username=rob type=on schedule

id=peek secs="2010-03-07 11:36:44" mk=3 route=9 username=joe type=off schedule

id=dest username=sam secs="2010-03-07 11:36:44" route=10 mk=10 type=on schedule

id=arr secs="2010-03-08 11:51:30" route=12 mk=10 username=sam type=on schedule

];

// extract the data as name value pairs

T_Data:

LOAD SubField(ValuePair, '=', 1) As Name,

Replace(SubField(ValuePair, '=', 2), '_', ' ') As Value,

SubField(SubField(Fields, ' ', 1), '=', 2) As _row

;

LOAD Fields,

SubField(Fields, ' ') As ValuePair

Resident Raw;

//Convert the names to field names

Data:

Generic LOAD _row, Name, Value

Resident T_Data;

// Clean up

DROP Table T_Data;

DROP Table Raw;

Re: Text files.

jonathandienst — Mon, 05 Oct 2015 13:30:01 GMT

The previous script will create a star type structure. It may be ugly, but it does work.

However, you cannot save it in a qvd. If that is your goal, you will need to fold the star down into a single table:

Final:

NoConcatenate

LOAD * Resident Data.row;

Join(Final) LOAD * Resident Data.id;

Join(Final) LOAD * Resident Data.secs;

Join(Final) LOAD * Resident Data.route;

Join(Final) LOAD * Resident Data.mk;

Join(Final) LOAD * Resident Data.username;

Join(Final) LOAD * Resident Data.type;

DROP Table Data.row;

DROP Table Data.id;

DROP Table Data.secs;

DROP Table Data.route;

DROP Table Data.mk;

DROP Table Data.username;

DROP Table Data.type;

DROP Field _row;

Note this will not create a more efficient model, but it will put everything in a single table which could be saved to a qvd or qvx file.

Re: Text files.

raadwiptec — Tue, 06 Oct 2015 07:10:14 GMT

hi jonanthan ,

iam not able to understand your code..? Are you trying to make a table for each column?

LOAD 'row=' & RowNo() & ' ' & SubField(Fields, '"', 1) & Replace(SubField(Fields, '"', 2), ' ', '_') & SubField(Fields,'"', 3) As Fields

what is this subfield here..

Re: Text files.

raadwiptec — Tue, 06 Oct 2015 07:17:41 GMT

and also what should be in the place of field

for ex: when I try to import the file into qlikview I taken the none option ..so my header looks like @1,@2 etc

but if I choose embedded then my column looks like id=transit,...etc

Re: Text files.

raadwiptec — Tue, 06 Oct 2015 11:21:49 GMT

hi jonanthan,

rather than an inline can you provide me the code when you are pulling the same data using loading a file.