Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
parispantelis
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
marcus_sommer

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
marcus_sommer

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

parispantelis
Contributor II
Contributor II
Author

worked !!!

thanks Marcus