Substring tricky in Qlikview?

[#1]

I'm trying to bulid an application where I want to calculate sales activities. These are stored in a quite tricky database. Looks like this:

I want to use Qlikview for 3 things (to start with):

Dates:
2005-08-24
etc.....

Salesmen:
SD:
DW:
KMT:
etc....

Activities:
- Phonecall (out):
- Phonecall (in):
- Sent Quotation:
etc.....

I think I can use the index function and the substringcount function. Any ideas what the best way of doing this?

Thanks!

[disqr_rm]

Can you post a small csv file with some data? It would be easire than taking guesses on the delimiters.

[rwunderlich · Accepted solution]

It looks like the length of the date is fixed, with the remainder of the fields being delimited by ":'. Should be pretty easy using subfield to pick out the fields.

date(date#(left(History,10),'YYYY-MM-DD') as Date
subfield(mid(History,11),':',1) as Salesman
subfield(History, ':', 2) as Activity
subfield(History, ':', 3) as Who

etc

-Rob

[#4]

Well I hope it was so easy, but the Database has an awful construction. The Dates can be anywhere in the string, not the first 10 positions. A new date (and activities) can be found in the same string.

I've created an example of this. I've added a new activity as "ADMIN" (As salesman)
Each Value starts with

Date Salesman: Activity: Contact: TextextexttexttextDate Salesman: Activity: Contact:

Example files attached.

Thanks guys!

[View:http://community.qlik.com/cfs-file.ashx/__key/CommunityServer.Discussions.Components.Files/11/3124.Sales.zip:550:0]

[rwunderlich]

In the sample you uploaded, there is a hard CRLF at the end of each entry. So you could spilt them like:

LOAD *,
date(date#(left(History,10),'YYYY-MM-DD')) as Date,
subfield(mid(History,11),':',1) as Salesman,
subfield(History, ':', 2) as Activity,
subfield(History, ':', 3) as Who
;
LOAD Type,
[Key 1],
[Key 2],
subfield(History, chr(13) & chr(10)) as History
FROM
Book4.xls
(biff, embedded labels, table is Sheet1$);

One entry has "IMPORTED FROM FILE" where the date should be. You'll have to filter these out but I don't know what you want to do for the date.

If there is no CRLFor other entry delimiter in your real data, the only other idea I have would be to use Regular Expressions (VB) to break at the nnnn-nn-nn date pattern.

-Rob

[disqr_rm]

Look here: http://community.qlik.com/forums/p/18107/70780.aspx#70780

This is discussed previousely and solution was validated. 🙂

[#7]

Hi thanks for all help.

Solved the calculation with

Aggr

(sum(substringcount (Activity, 'Phonecall')),Salesman)

etc...

Tanks!