topic Re: How to combine multiples/filter? in QlikView

How to combine multiples/filter?

Thu, 31 Dec 2015 15:50:45 GMT

So I'm relatively new to coding entirely..

My dashboard has many users, which have many courses we enroll them in. Sometimes their courses may be cancelled, be dropped, but what I want to see is, if they completed it, it is all irreverent. For example:

Steve Intro to Medical Terminology Completed

Steve Intro to Medical Terminology Open

James Body Systems Dropped

James Intro to Medical Teminology Dropped

Karen Intro to Medical Terminology Completed

So if I am trying to pull up a report, and I want to see who has completed "Intro to Med Terms," it should only be Steve and Karen. The code I have been using is:

Max(Count(Course_Status="completed",1,0))

Any advice?

Re: How to combine multiples/filter?

sunny_talwar — Thu, 31 Dec 2015 16:32:24 GMT

You can also use set analysis for this:

to list the names

=Concat(DISTINCT {<Name = p({<CourseStatus = {'Completed'}>})>}Name, ', ')

to count the names

=Count(DISTINCT {<Name = p({<CourseStatus = {'Completed'}>})>}Name)

Re: How to combine multiples/filter?

sunny_talwar — Thu, 31 Dec 2015 16:36:00 GMT

Or like this in a straight table:

Re: How to combine multiples/filter?

Thu, 31 Dec 2015 16:42:56 GMT

I definitely appreciate the quick reply, but maybe I should clarify a bit.

My current qlikview displays:

Hospital Name Email Course Course Status Program Enroll Program Complete

TX James *** 1 complete 11/12 12/11

TX James *** 1 open 11/12 12/11

TX James *** 2 complete 11/12 12/11

TX James *** 2 dropped 11/12 12/11

TX James *** 3 open 11/12 12/11

So I am trying to combine all like courses to see what their status is as completed being prefered. For example:

Hospital Name Email Course Course Status Program Enroll Program Complete

TX James *** 1 complete 11/12 12/11

TX James *** 2 complete 11/12 12/11

TX James *** 3 open 11/12 12/11

So I have been trying to put this in the properties table, as a conditional dimension.

See how green I am in all of this?

Re: How to combine multiples/filter?

maxgro — Thu, 31 Dec 2015 16:51:23 GMT

I suggest to

- add a tablebox with all your fields

- add 3 listbox

and then start clicking on the listbox to filter and get the result you want, just click on Completed and Intro to......

Re: How to combine multiples/filter?

Thu, 31 Dec 2015 16:58:21 GMT

Thanks Max, I have incorporated those options, however as in my reply to Sunny, there comes the case where there are multiple status entries, and I am trying to combine those entries.

Re: How to combine multiples/filter?

sunny_talwar — Thu, 31 Dec 2015 18:15:35 GMT

Try this as your calculated dimension:

=Aggr(If(SubStringCount(Concat(DISTINCT [Course Status], '|'), 'complete') = 1, 'complete', [Course Status]), Course)

Re: How to combine multiples/filter?

Thu, 31 Dec 2015 18:29:12 GMT

Perfect, thank you very much!

Re: How to combine multiples/filter?

sunny_talwar — Thu, 31 Dec 2015 18:39:58 GMT

Not a problem. I am glad I was able to help

Best,

Sunny