Large text need to split into seperate fields

[cmorton1964]

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

<questionnaire questionnaire_id="PROJECT - SMI"><question quest_name="SMI Number" value="0212"/><question quest_name="Requester" value="ALAN ROPER 150605"/><question quest_name="Agreement Number" value="4610001895"/><question quest_name="Contract" value="%27Facilities-Management"/><question quest_name="Contractor" value="%27Mite"/><question quest_name="Contract condition" value="%2716.2"/><question quest_name="End Client" value="%27Sellafield"/><question quest_name="Building Manager" value="JESSICA SMITH 192436"/><question quest_name="Out of hours work" value="No"/><question quest_name="Design Request only" value="No"/><question quest_name="SMI Priority" value="HIGH"/><question quest_name="CEMAR Reference" value="PICD 5145"/><question quest_name="Contractor Finance Code" value="MFM-03011-00001-03-539"/><question quest_name="Quoted Value" value="9669.69"/><question quest_name="Final invoice Value" value="9669.69"/></questionnaire>

[asinha1991]

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,

[Brett_Bleess]

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

[marcus_sommer]

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

[cwolf]

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;