topic Re: Data ?parsing? related problem in QlikView

Data ?parsing? related problem

Wed, 18 May 2016 12:37:45 GMT

Hello,

I am having a bit of a problem.

I have data that looks something like this (first three lines):

ID	TIMESTAMP	A	B
1	18.5.2016 18:01	CLERK	FIRST TOUCH
1	19.5.2016 18:01	BUSINESS	APPROVE
1	20.5.2016 18:03	CLERK	ARCHIVE

We would like to show the most related combination path that is used on this process.

So it would here be Clerk-First Touch -> Business-Approve -> Clerk Archive

I do not now how to produce that kinda field even in Qlik..

I have managed to create a timeline chart, with barchart, that from where we can see the process for each ID when chosing ID.

It is sorted by timestamp, so it is pretty easy to do.

But is it possible to create that process path field in somehow in qlik?

And process path that is aggregated over time, so if we would like to view only certain month or year..

I know this is a lot of asking, but I have absolutely no idea how to do this

P.S. Sample file attached.

Cheers,

Niko

Re: Data ?parsing? related problem

swuehl — Wed, 18 May 2016 12:43:01 GMT

Maybe something like this e.g. as expression in a chart with dimension ID

=Concat( A & '-' & B, ' -> ', TIMESTAMP)

ID	Process
1	CLERK-FIRST TOUCH -> BUSINESS-APPROVE -> CLERK-ARCHIVE
2	CLERK-FIRST TOUCH -> CLERK-FIRST TOUCH -> CLERK-ARCHIVE
3	CLERK-FIRST TOUCH -> BUSINESS-REVIEW -> CLERK-FIRST TOUCH
4	CLERK-FIRST TOUCH -> BUSINESS-APPROVE -> CLERK-ARCHIVE
5	CLERK-ARCHIVE -> CLERK-FIRST TOUCH -> BUSINESS-APPROVE

Re: Data ?parsing? related problem

Wed, 18 May 2016 12:52:53 GMT

Very cool!

Is there a way to get an AVG or most popular of these paths?

for example process CLERK-FIRST TOUCH -> BUSINESS-APPROVE -> CLERK-ARCHIVE has 2 lines, others 1.

Cheers,

Niko

Re: Data ?parsing? related problem

swuehl — Wed, 18 May 2016 13:02:06 GMT

Maybe using the Mode() function:

=Mode(Aggr(Concat( A & '-' & B, ' -> ', TIMESTAMP),ID))

e.g. in a text box.

Re: Data ?parsing? related problem

swuehl — Wed, 18 May 2016 13:10:10 GMT

Mode() will only show a result if there are no ties in highest frequency for distinct processes.

Alternatively, create a chart with a calculated dimension and a frequency count, like

=Aggr( Concat( A & '-' & B, ' -> ', TIMESTAMP),ID)	Count(DISTINCT ID)
	5
CLERK-FIRST TOUCH -> BUSINESS-APPROVE -> CLERK-ARCHIVE	2
CLERK-FIRST TOUCH -> CLERK-FIRST TOUCH -> CLERK-ARCHIVE	1
CLERK-FIRST TOUCH -> BUSINESS-REVIEW -> CLERK-FIRST TOUCH	1
CLERK-ARCHIVE -> CLERK-FIRST TOUCH -> BUSINESS-APPROVE	1

Re: Data ?parsing? related problem

Thu, 19 May 2016 04:07:12 GMT

Wow, thanks for thousand swuehl!

That is exactly what I was looking for.

Hopefully some day, I will be as smart as you!

The aggr() function is a little bit unknown for me, so this solution is a bit unknown to me Have to take a better look on to it, how it works.

btw, the Mode() thing works like a charm also.

How does the mode handles the situation where there will be a tie, will it show all the values that has the same value?

Cheers,

Niko

Re: Data ?parsing? related problem

swuehl — Thu, 19 May 2016 08:22:38 GMT

btw, the Mode() thing works like a charm also.

How does the mode handles the situation where there will be a tie, will it show all the values that has the same value?

No, unfortunately it will return nothing, NULL.

Re: Data ?parsing? related problem

Thu, 19 May 2016 08:59:48 GMT

I guess there is no way to take something like first() then? or first index?

Well, this has helped me a lot already !

Thanks!

Cheers,

Niko

Re: Data ?parsing? related problem

swuehl — Thu, 19 May 2016 09:04:21 GMT

Well, there probably are solutions (probably a little more complex since your process values are dynamically built) using (more) advanced aggregations.

If you can live for now with mode() and the frequency table, then I would start with that.

Re: Data ?parsing? related problem

Thu, 19 May 2016 09:28:47 GMT

Yes I totally can live with the frequency table and mode() solutions !

Cheers,

Niko

Re: Data ?parsing? related problem

Fri, 20 May 2016 06:27:53 GMT

I was wondering, is there a possibility to have a big number out of that most popular path?

So a count from that mode() script? It has aggr() already there, so I think the count is not something that can be implemented to that?

Cheers,

Niko

Re: Data ?parsing? related problem

swuehl — Fri, 20 May 2016 08:54:40 GMT

You can limit the straight table using dimension limits to the max process count, then adjust the format to get something like