text data splitting into columns and rows!

[raadwiptec]

Hi all,

could somebody suggest a solution.

the source is a flat file and data is as below

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

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 in this column

in qlikview if iam loading this I would like to have as below

id  ¦   secs ¦

[#2]

You can set the Columns and Values as follows:-

ID Column Name would be:- 

=subfield( 'id=transit secs="2010-03-07 11:36:44" route=6 mk=10 username=sam type=on schedule','=',1)

Value would be:- 

=subfield(subfield( 'id=transit secs="2010-03-07 11:36:44" route=6 mk=10 username=sam type=on schedule','=',2),' ',1)

Secs Column Name would be:-

subfield(subfield( 'id=transit secs="2010-03-07 11:36:44" route=6 mk=10 username=sam type=on schedule','=',2),' ',2)

Value would be:-

=Subfield( 'id=transit secs="2010-03-07 11:36:44" route=6 mk=10 username=sam type=on schedule','"',2)

[MarcoWedel]

Hi,

maybe helpful:

Parsing fields from a string

regards

Marco

[raadwiptec]

thanks Claran for the nice expalanation/solution  and all for nice suggestions..