topic Substring tricky in Qlikview? in QlikView

Substring tricky in Qlikview?

Fri, 24 Jul 2009 03:11:16 GMT

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!

Substring tricky in Qlikview?

disqr_rm — Fri, 24 Jul 2009 03:45:44 GMT

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

Substring tricky in Qlikview?

rwunderlich — Fri, 24 Jul 2009 03:57:17 GMT

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

Substring tricky in Qlikview?

Fri, 24 Jul 2009 06:08:18 GMT

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]

Substring tricky in Qlikview?

rwunderlich — Fri, 24 Jul 2009 06:39:21 GMT

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

Substring tricky in Qlikview?

disqr_rm — Fri, 24 Jul 2009 07:56:39 GMT

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

This is discussed previousely and solution was validated. 🙂

Substring tricky in Qlikview?

Fri, 24 Jul 2009 09:45:06 GMT

Hi thanks for all help.

Solved the calculation with

Aggr

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

etc...

Tanks!