Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
!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.
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);
sorry How do I change it back to unanswered, I have also attached the qvd and script Thanks
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?
Hello, Asad.
I believe the attached qvw does what you want. It uses subfield as was sugested.
Cheers!
Thank You SO MUCH I had received headache upon headache, you are officially my advil Thank you .
You're welcome! Glad I could be of help.