Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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 ...
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
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;
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.
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..
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
hi jonanthan,
rather than an inline can you provide me the code when you are pulling the same data using loading a file.