Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
IMPORTANT: Upcoming LEF Database Maintenance, Oct. 3rd - SEE DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor II
Contributor II

Read fields and values from a record

hi,

im not a Master in scriping and i have the following problem:

i have a table (on sql server) with 3 fields:

1. Record ID

2. Timestamp

3. Data

inside the 3rd field i call "Data", i have several data like Field1:SomeValue, Field2: SomeValue,...,Field_N:SomeValue

the fields inside "Data" are not the same (in many records they start with "Field2" or "Field3" etc) and previous fields are missing.

I had an idea to export this table to TXT file and import again spliting data field to many Fields like @1, @2, @3 etc.

but inside 1st field @1 in some records i will have from "Data" the "Field1" and in some records i will have "Field2" or "Field3" etrc.

is there a way to read directly from "Data" field the several Fields and Values ?

Format is allways like Field1:SomeValue,Field2:SomeValue,Field3:SomeValue ....

The number of Fields inside the "Data" field are not specific

Any help will be appreciated.

1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

You could split them within qv in a way like this:

t1:

Load

     [Record ID], Timestamp,

     subfield(Data, ':', 1) as Fieldname,

     subfield(Data, ':', 2) as Fieldvalue;

Load [Record ID], Timestamp, subfield(Data, ',') as Data;

SQL Select * From db;

I'm not absolutely if the first subfield-statement worked within a preceeding-load (if not you could replace it with a normal resident load). Are Fieldname and Fieldvalue not real fields + values else more a kind of category with value you are already finish. Are this real fields you need a further generic load to create them as fields, see: Generic Load.

- Marcus

View solution in original post

2 Replies
MVP & Luminary
MVP & Luminary

You could split them within qv in a way like this:

t1:

Load

     [Record ID], Timestamp,

     subfield(Data, ':', 1) as Fieldname,

     subfield(Data, ':', 2) as Fieldvalue;

Load [Record ID], Timestamp, subfield(Data, ',') as Data;

SQL Select * From db;

I'm not absolutely if the first subfield-statement worked within a preceeding-load (if not you could replace it with a normal resident load). Are Fieldname and Fieldvalue not real fields + values else more a kind of category with value you are already finish. Are this real fields you need a further generic load to create them as fields, see: Generic Load.

- Marcus

View solution in original post

Highlighted
Contributor II
Contributor II

worked !!!

thanks Marcus