topic Re: Re: Need help creating Field From within Field with Text in QlikView

Need help creating Field From within Field with Text

Fri, 30 May 2014 12:53:08 GMT

!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.

Re: Need help creating Field From within Field with Text

PrashantSangle — Fri, 30 May 2014 13:07:03 GMT

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,

Re: Need help creating Field From within Field with Text

Fri, 30 May 2014 13:51:34 GMT

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.

Re: Re: Need help creating Field From within Field with Text

its_anandrjs — Fri, 30 May 2014 14:42:48 GMT

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;

Re: Need help creating Field From within Field with Text

Fri, 30 May 2014 15:15:59 GMT

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.

Re: Re: Need help creating Field From within Field with Text

its_anandrjs — Fri, 30 May 2014 15:22:02 GMT

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*');

Re: Need help creating Field From within Field with Text

Fri, 30 May 2014 15:35:56 GMT

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

Re: Need help creating Field From within Field with Text

PrashantSangle — Fri, 30 May 2014 16:03:37 GMT

Hi,

If possible can you share your apps.

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

Regards,

Re: Need help creating Field From within Field with Text

Fri, 30 May 2014 16:11:16 GMT

just shared it in first post

Re: Need help creating Field From within Field with Text

PrashantSangle — Fri, 30 May 2014 16:23:52 GMT

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

Re: Re: Need help creating Field From within Field with Text

its_anandrjs — Fri, 30 May 2014 16:33:39 GMT

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);

Re: Need help creating Field From within Field with Text

Fri, 30 May 2014 17:32:12 GMT

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

Re: Need help creating Field From within Field with Text

Fri, 30 May 2014 19:38:45 GMT

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?

Re: Re: Need help creating Field From within Field with Text

Anonymous — Fri, 30 May 2014 21:04:51 GMT

Hello, Asad.

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

Cheers!

Re: Need help creating Field From within Field with Text

Mon, 02 Jun 2014 12:23:58 GMT

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

Re: Need help creating Field From within Field with Text

Anonymous — Mon, 02 Jun 2014 12:26:27 GMT

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