Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us at the Cloud Data and Analytics Tour! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Data ?parsing? related problem

Hello,

I am having a bit of a problem.

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

   

IDTIMESTAMPAB
118.5.2016 18:01CLERKFIRST TOUCH
119.5.2016 18:01BUSINESSAPPROVE
120.5.2016 18:03CLERKARCHIVE

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

1 Solution

Accepted Solutions
swuehl
MVP
MVP

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-ARCHIVE2
CLERK-FIRST TOUCH -> CLERK-FIRST TOUCH -> CLERK-ARCHIVE1
CLERK-FIRST TOUCH -> BUSINESS-REVIEW -> CLERK-FIRST TOUCH1
CLERK-ARCHIVE -> CLERK-FIRST TOUCH -> BUSINESS-APPROVE1

View solution in original post

11 Replies
swuehl
MVP
MVP

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

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

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

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
MVP
MVP

Maybe using the Mode() function:

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

e.g. in a text box.

swuehl
MVP
MVP

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-ARCHIVE2
CLERK-FIRST TOUCH -> CLERK-FIRST TOUCH -> CLERK-ARCHIVE1
CLERK-FIRST TOUCH -> BUSINESS-REVIEW -> CLERK-FIRST TOUCH1
CLERK-ARCHIVE -> CLERK-FIRST TOUCH -> BUSINESS-APPROVE1

View solution in original post

Not applicable
Author

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
MVP
MVP

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.

Not applicable
Author

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
MVP
MVP

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.

Not applicable
Author

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

Cheers,

Niko