topic Re: text data splitting into columns and rows! in QlikView

text data splitting into columns and rows!

raadwiptec — Mon, 05 Oct 2015 09:37:35 GMT

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 ¦

Re: text data splitting into columns and rows!

raadwiptec — Mon, 05 Oct 2015 12:18:57 GMT

hi all,

any suggestions here?

Re: text data splitting into columns and rows!

ciaran_mcgowan — Mon, 05 Oct 2015 12:39:55 GMT

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

Re: text data splitting into columns and rows!

raadwiptec — Mon, 05 Oct 2015 13:06:51 GMT

its not a text ..its a flat file. I could see the properties as windows shell common

Re: text data splitting into columns and rows!

Colin-Albert — Mon, 05 Oct 2015 13:25:51 GMT

Have a look at the subfield() or textbetween() commands.

Re: text data splitting into columns and rows!

ciaran_mcgowan — Mon, 05 Oct 2015 13:27:05 GMT

Can you send me a version of the file?

Re: text data splitting into columns and rows!

raadwiptec — Mon, 05 Oct 2015 13:41:30 GMT

file version is blank

Re: text data splitting into columns and rows!

ciaran_mcgowan — Mon, 05 Oct 2015 14:31:46 GMT

I don't know what you mean. If you post an example of the file, we might be able to help you more.

Re: text data splitting into columns and rows!

raadwiptec — Tue, 06 Oct 2015 07:00:42 GMT

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

Re: text data splitting into columns and rows!

ciaran_mcgowan — Tue, 06 Oct 2015 15:27:40 GMT

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.

Re: text data splitting into columns and rows!

raadwiptec — Wed, 07 Oct 2015 07:24:00 GMT

hi claran,

As you mentioned can you please share how do I load each row as Data . so in my example if iam trying to load the file containing the below data as a atable

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

I have 2 options.

while loading the file if I give an option of none.. my columns look like @1,@2,@3

if I give option of embedded labels I get my column as

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

Re: text data splitting into columns and rows!

gautik92 — Wed, 07 Oct 2015 07:30:06 GMT

use textbetween

Re: text data splitting into columns and rows!

raadwiptec — Wed, 07 Oct 2015 08:10:50 GMT

hi claran

as you said in ur earlier post iam not able to load @1:n -how do I do this?

TableName:
LOAD [@1:n] AS data
FROM
[test.txt]
(fix, codepage is 1252);

I basically tried to concatenate the tables like below

TableName:
LOAD

@1&@2&@3&@4&@5&@6&@7&@8&@9&@10&@11 as data

FROM xxxx.txt

After which I tried to load the CleanData. But there iam getting the values also concatenated.

Re: text data splitting into columns and rows!

Digvijay_Singh — Wed, 07 Oct 2015 08:41:06 GMT

Based on amazing sugestion from ciaran.mcgowan‌ , I created the sample qvw. I think we shouldn't worry how data is loaded, let it be one line per record as suggested-

Input:

LOAD @1:n as data

FROM

(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), 'mk=', 1) AS Route,

SubField(SubField(data, 'mk=', 2), 'username=', 1) AS mk,

SubField(SubField(data, 'username=', 2), 'type=', 1) AS Username,

Subfield(data, 'type=', 2) AS Type

Resident Input;

I just included mk also as a column in last suggested script by ciaran.mcgowan

Here are the qvw and sample file.

Re: text data splitting into columns and rows!

raadwiptec — Wed, 07 Oct 2015 09:30:02 GMT

hi Ciaran,

very nice.. Iam getting good results.. I need to test with the complete file. I will let you know. can you please tell me the code ...how subfield is managing this?

thanks digvijay..

the problem with my load earlier was

(fix, codepage is 1252); I was using --

(txt, codepage is 1252, no labels, delimiter is spaces, msq); where I wan not able to use the :n values

Re: text data splitting into columns and rows!

ciaran_mcgowan — Wed, 07 Oct 2015 10:10:02 GMT

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.

Re: text data splitting into columns and rows!

raadwiptec — Wed, 07 Oct 2015 10:37:32 GMT

is it possible to take the year ,month and date from the secs column - ="2010-03-07 11:36:44"

as it is in quotes .. it is not recognizing if I give

load

year(Secs) as year

resident clean Data

Re: text data splitting into columns and rows!

Digvijay_Singh — Wed, 07 Oct 2015 10:47:02 GMT

You can remove " by purgechar and then interpret it as Timestamp.

Timestamp#(PurgeChar(SubField(SubField(data, 'secs=', 2), 'route=', 1),'"'),'YYYY-MM-DD hh:mm:ss') AS Secs,

Re: text data splitting into columns and rows!

Colin-Albert — Thu, 08 Oct 2015 08:25:23 GMT

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

Hi Ceiran,

The textbetween() function can do this eliminating the need to use two subfield functions

textbetween(data, 'id=', 'secs=', ) as Secs

Re: text data splitting into columns and rows!

ciaran_mcgowan — Thu, 08 Oct 2015 08:32:36 GMT

Good to know!

Thanks.