Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

parispantelis
New 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 Field1Smiley FrustratedomeValue, Field2: SomeValue,...,Field_NSmiley FrustratedomeValue

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 Field1Smiley FrustratedomeValue,Field2Smiley FrustratedomeValue,Field3Smiley FrustratedomeValue ....

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

Any help will be appreciated.

Tags (3)
1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

Re: Read fields and values from a record

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

2 Replies
MVP & Luminary
MVP & Luminary

Re: Read fields and values from a record

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
New Contributor II

Re: Read fields and values from a record

worked !!!

thanks Marcus