Text files.

[raadwiptec]

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

[Gysbert_Wassenaar]

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 ...

[raadwiptec]

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

[jonathandienst]

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;

[jonathandienst]

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.

[raadwiptec]

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..

[raadwiptec]

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

[raadwiptec]

hi jonanthan,

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