Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Need help creating Field From within Field with Text

!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 CompanyTopicRequest TypeAddressCityStateZIP
DD01Customer ServiceBudget Billing4 Blah st.BordanPA
CR01Bill Inquiry85 Blah stCrowleyPA22242

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.

1 Solution

Accepted Solutions
Anonymous
Not applicable
Author

Hello, Asad.

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

Cheers!

View solution in original post

15 Replies
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,

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
Not applicable
Author

qv Help.gif

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.  qv Help baddata.gif

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;

Not applicable
Author

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

Not applicable
Author

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

PrashantSangle

Hi,

If possible can you share your apps.

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

Regards,

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
Not applicable
Author


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

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂