Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 ¦
Hi raadwiptec,
Glad to hear it. Subfield works by splitting up a field into smaller pieces by looking through a field for a specified value, once it finds it, it will return everything to the left or right of it.
If you had a string called FRUIT that contained the following vaule: "Apple-Orange-Grape-Pear-Banana" it can be split up using the "-" as a separator like this:
Subfield(FRUIT,'-',1) AS Fruit1, will return Apple
Subfield(FRUIT,'-',2) AS Fruit2 will return Orange
Subfield(FRUIT,'-',3) AS Fruit3, will return Grape
Subfield(FRUIT,'-',4) AS Fruit4, will return Pear
Subfield(FRUIT,'-',5) AS Fruit5, will return Banana
In your case, I used Subfield twice so it will search for everything to the left of the desired text and everything to the right and return what's in between the two:
SubField(SubField(data, 'id=', 2), 'secs=', 1) AS ID
Breakdown:
Original: "id=transit secs="2010-03-07 11:36:44" route=6 mk=10 username=sam type=on schedule"
SubField(data, 'id=', 2) would return everything to the right of the "id=" text
Result: "transit secs="2010-03-07 11:36:44" route=6 mk=10 username=sam type=on schedule"
SubField(data, 'secs=', 1) would return everything to the left of the "secs=" text
Result: "id=transit "
Combining the two tells it for first search for everything to the right of "id=" and then, from the result of that, everything to the left of the "secs=" which is why SubField(SubField(data, 'id=', 2), 'secs=', 1) will return transit for the above example.
hi all,
any suggestions here?
You could try loading all values into a single table and doing something like this (in this case the field I load everything into is called "data"):
TableName:
LOAD [@1:n] AS data
FROM
[test.txt]
(fix, codepage is 1252);
CleanData:
LOAD
SubField(SubField(data, 'id=', 2), 'secs=', 1) AS ID,
SubField(SubField(data, 'secs=', 2), 'route=', 1) AS Secs,
SubField(SubField(data, 'route=', 2), 'username=', 1) AS Route,
SubField(SubField(data, 'username=', 2), 'type=', 1) AS Username,
Subfield(data, 'type=', 2) AS Type
Resident
TableName;
That's assuming that you're loading it using a .txt file
its not a text ..its a flat file. I could see the properties as windows shell common
Have a look at the subfield() or textbetween() commands.
Can you send me a version of the file?
file version is blank
I don't know what you mean. If you post an example of the file, we might be able to help you more.
sorry you were asking me the copy of this file. as mentioned earlier the data looks fully similar like this
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
and is more than 15mb I have taken few values. you can consider the same..
and this is a flat file not really a text file...
so I would like the output from the file like below
id should be the column and in the value it should be 'transit'
secs should be the column and in the value it should be 2010-03-07 11:36:44
simililarly...
id ¦ secs ¦ route ¦ mk ¦username
transit
OK, forgot about the text file, that's just what I used to recreate your issue.
Can you load the flat file into Qlikview? If so, load each record into a table. In my example below, I loaded it into a table called TableName. I called each row "data".
I then cleaned it using the below script.
CleanData:
LOAD
SubField(SubField(data, 'id=', 2), 'secs=', 1) AS ID,
SubField(SubField(data, 'secs=', 2), 'route=', 1) AS Secs,
SubField(SubField(data, 'route=', 2), 'username=', 1) AS Route,
SubField(SubField(data, 'username=', 2), 'type=', 1) AS Username,
Subfield(data, 'type=', 2) AS Type
Resident
TableName;
If you do the same, this will separate out each row into the 5 different fields you are looking for and you can create a table using them.