topic Re: Large text need to split into seperate fields in QlikView

Large text need to split into seperate fields

cmorton1964 — Sat, 16 Nov 2024 02:19:16 GMT

I have a large text which is deposited in DB which is the answers to multiple questions from custom fields added to a form. The data is stored as a single blob of text.

I want to split this out to different fields

Question name would be Field name eg SMI Number value would be 0212 can any advise on method to do this ?

See sample below

Re: Large text need to split into seperate fields

asinha1991 — Thu, 03 Oct 2019 09:35:33 GMT

you need to write a parser,

first parse text into different rows with subfield function (maybe on '>'),

then to assign these values to different fields

you can use mid with index on substring and value

you can try to explore regular expression

RegExTest function,

Re: Large text need to split into seperate fields

Brett_Bleess — Thu, 10 Oct 2019 12:18:35 GMT

Craig, did Anand's post help you with things? If so, be sure to give him credit by using the Accept as Solution button on his post. If you have gone a different route, consider sharing that and then mark it with the button so others will know how you solved things, and if you are still trying to sort things out, leave us an update, so we can try to come up with some other ideas. I am pretty sure Anand's approach is likely the best way to go, but I am not exactly an expert on development, so others may have some further ideas. My post will kick things back up today, so we will see if anyone else has anything for you. About the best I have otherwise would be the Design Blog area, below is the base URL where you can search just in that area, you might be able to get some ideas from posts there...

https://community.qlik.com/t5/Qlik-Design-Blog/bg-p/qlik-design-blog

Sorry I do not have anything better for you.

Regards,
Brett

Re: Large text need to split into seperate fields

marcus_sommer — Fri, 11 Oct 2019 06:34:36 GMT

I think I wouldn't create multiple fields within a crosstable else I would create a stream-table, with something like this:

t1: load
KEY, mod(rowno(), 2) as Filter,
subfield(subfield(YourField, '>'), '=', 1) as Attribut, subfield(subfield(YourField, '>'), '=', 2) as Value
from Source;

t2: noconcatenate load * resident where Filter = 1;

drop tables t1;

Further you may apply various string-functions like replace/purgechar/trim/mid and so on to remove chars like <>/%.

- Marcus

Re: Large text need to split into seperate fields

cwolf — Fri, 11 Oct 2019 13:30:03 GMT

Your field has a xml content, so you can use the xml-parser to split it in separate fields.
The only thing you have to do, is to expand the xml content with the primary key of the table.

For Example:

DB_TABLE: Load RecNo() as primary_key, * INLINE [ Field1,Field2,Field3, 123,aaa,'<questionnaire questionnaire_id="PROJECT - SMI">...' 456,bbb,'<questionnaire questionnaire_id="PROJECT - ABC">...' 789,bbb,'<questionnaire questionnaire_id="PROJECT - DEF">...' ]; XML: load '<data>' & Concat(xmlField) & '</data>' as xmlField; Load '<record primary_key="' & primary_key & '">' & Field3 & '</record>' as xmlField resident DB_TABLE; question: LOAD quest_name, value, %Key_record_EF79C51078A65475 as xml_key From_Field(XML,xmlField) (XmlSimple, Table is [data/record/questionnaire/question]); record: LOAD primary_key, [questionnaire/questionnaire_id] as QUEST.questionnaire_id, %Key_record_EF79C51078A65475 as xml_key From_Field(XML,xmlField) (XmlSimple, Table is [data/record]); DROP Table XML; left join(DB_TABLE) load * resident record; for i = 1 to fieldvaluecount('quest_name'); let field = fieldvalue('quest_name',$(i)); left join(DB_TABLE) load xml_key, value as "QUEST.$(field)" resident question where quest_name='$(field)'; next drop tables record,question; drop field xml_key;