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.

[PrashantSangle]

Hi,

Use Subfield()

Like Suppose You want Operating Company

Then use

Subfield(Data,':',2)

Similarly for Topic

Use

Subfield(Data,':',3)

And So on

Regards,

[#3]

Maybe I did something wrong but When I added it , It gave me the wrong data , there are a lot of messages that have no data pertaining to operating company but those that do all have that format in line 1 and 2 but there are a lot that are like line 3 which is useless data. 

[its_anandrjs]

You have to use Subfield() many times as there are many spaces in the msgbody then try to use by below load script in your application

RawSource:

LOAD * Inline

[

MsgBody

Operating Company:  CR01      Topic: Billing/Rate          Requst Type: Bill Inquiry     Address  85 Blah st.      City Crowley State: PA  Zip: 224442    Country: USA

Operating Company:  DD01      Topic: Customer Service        Request Type: Budget Billing     Address  4 Blah st.      City Bordan State: PA  Zip: 22222    Country: USA

Sent: Friday, May 11, 2014  11:20: AM  TO Rodger   Forsester Request and include request Number below, Thank You for visiting.

] Where WildMatch(MsgBody,'Op*');

FinalTable:

LOAD

MsgBody,

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

Resident RawSource;

DROP Table RawSource;

[#5]

I Tried your example on a new qvw and it Worked Perfectly how ever when I add the Where condition to the table it removes all the rows.

[its_anandrjs]

Thanks but you need to check the condition for MsgBody for where condition i put where condition for load only string which is start with Operating some thing like below script try with not putting this

Where WildMatch(MsgBody,'Op*');

[#7]

Thank you for the guidance and Sorry  but I am still having problems, Here is what I have, I apologize for the trouble. Thanks

[PrashantSangle]

Hi,

If possible can you share your apps.

If not then give us some dummy data where we can work.

Regards,

[#9]

just shared it in first post

[PrashantSangle]

Hi,

Just sake of interest Why are you using

Where WildMatch(MsgBody,'Op*');

and If you are creating new Table from Existing one then use only one KeyField to associate them.

Or simply Use

Load * and then write your Subfield() expression in your script as you are trying right now.

And with out QVD I am not able to reload file.

Regards