Data ?parsing? related problem

[#1]

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

[swuehl]

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

[#3]

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

[swuehl]

Maybe using the Mode() function:

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

e.g. in a text box.

[swuehl · Accepted solution]

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

[#6]

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

[swuehl]

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.

[#8]

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

[swuehl]

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.

[#10]

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

Cheers,

Niko