Need help creating Field From within Field with Text

[#1]

!Hi Guys, I need some help, Actually a lot of help I am currently working on a database, where I need to extract information from an Email Client and one of the fields I need to Extract from is the actual email which contains some important info and some irrelevant stuff  Here is the example

MsgBody
Operating Company:  DD01      Topic: Customer Service        Request Type: Budget Billing     Address  4 Blah st.      City Bordan State: PA  Zip: 22222    Country: USA
Operating Company:  CR01      Topic: Billing/Rate          Requst Type: Bill Inquiry     Address  85 Blah st.      City Crowley State: PA  Zip: 224442    Country: USA
Sent: Friday, May 11, 2014  11:20: AM  TO Rodger   Forsester Request and include request Number below, Thank You for visiting.

I need to create Fields showing The fields underneath and make sure the other mails that are useless not to go in to the fields

22222Billing/Rate

Operating Company	Topic	Request Type	Address	City	State	ZIP
DD01	Customer Service	Budget Billing	4 Blah st.	Bordan	PA
CR01	Bill Inquiry	85 Blah st	Crowley	PA	22242

Any help would be greatly appreciated , thank you guys.  First time Posting a question, so if I placed it in the wrong place forgive me most of the problems I have are answered.. this is a bit of a doozy for me.

[its_anandrjs]

You can create this subfields in the current table it self no need to create another resident table and i use Where WildMatch(MsgBody,'Op*');  because your expected output is only for operating messages i suggest do not use it and see the load script for that

MailMessage:

LOAD

     MsgID AS %MsgID,

     MailStatus,

     IncidentID AS %IncidentID,

     CustEmailID AS %CustEmailID,

     DateRecv,

     DateSent,

     TimeZone,

     MsgFrom,

     MsgTo,

     MsgCC,

     MsgSubject,

     MsgBody,

     AgentID AS %AgentID,

     AgentName,

     msgType,

     MailBoxID AS %MailBoxID,

     ///////////////////////

     SubField(SubField(MsgBody,':',2),'  ',2) as [Operating Company],

     SubField(SubField(MsgBody,':',3),'  ',1) as Topic,

     SubField(SubField(MsgBody,':',4),'  ',4) as Address,

     SubField(SubField(SubField(MsgBody,':',4),'  ',7),' ',2) as City,

     SubField(SubField(MsgBody,':',5),' ',2) as State,

     SubField(SubField(MsgBody,':',6),' ',2) as Zip,

     Trim(SubField(MsgBody,':',7)) as Country

     ////////////////////////

FROM

[..\..\QVD\2ndLevelQVD\MailMessage.qvd]

(qvd);

[#3]

sorry How do I change it back to unanswered, I have also attached the qvd and script Thanks

[#4]

I left it in there after doing an inline that was my fault.  Is it just me or is this not possible with the database structure?

[Anonymous · Accepted solution]

Hello, Asad.

I believe the attached qvw does what you want. It uses subfield as was sugested.

Cheers!

[#6]

Thank You SO MUCH I had received headache upon headache, you are officially my advil Thank you .

[Anonymous]

You're welcome! Glad I could be of help.