Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

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.

Tags (2)
1 Solution

Accepted Solutions
bruno_montenegr
Not applicable

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

Hello, Asad.

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

Cheers!

15 Replies
PrashantSangle
Not applicable

Re: Need help creating Field From within Field with Text

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.
Not applicable

Re: Need help creating Field From within Field with Text

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
Not applicable

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

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

Re: Need help creating Field From within Field with Text

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
Not applicable

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

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

Re: Need help creating Field From within Field with Text

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
Not applicable

Re: Need help creating Field From within Field with Text

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.
Not applicable

Re: Need help creating Field From within Field with Text


just shared it in first post

PrashantSangle
Not applicable

Re: Need help creating Field From within Field with Text

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.