topic Re: Complicated selections--set analysis? in QlikView

Complicated selections--set analysis?

Sat, 01 Mar 2014 20:06:05 GMT

I have a scenario I need to add to a dashboard, and I wonder whether anyone else has run across this type of thing. Say you have a group of people who have all taken a number of different tests. I want to let the user choose a number of score ranges for various tests, and then have the dashboard return the set of people whose scores fell into those ranges, so they can see what their score was on another test. The score ranges and number of tests need to be under the user’s control, so they can write their own formula.

For example:

Give me a list of people whose score on test 1 is between 90 and 100, whose score on test 2 is 50, and whose score on test 3 is between 80 and 85, and tell me what their scores were on test 4.

All the test names are in one field, Test, and all the scores for all the tests are in another field, Score. Each Test and Score is linked by the person taking the test and the date they took it. I can’t just select values in Test and values in Score, since I will have multiple possible values in Score that will only be relevant when they correspond to a particular Test. There are hundreds of existing Tests and new ones being created all the time, so the set of tests any person has taken will vary.

What I’m looking for is not an aggregation—I’m looking to find the set of ID numbers of the test takers who meet all the criteria, so I can see what their other characteristics are. I want the score on the last-named test, but I might also want to know their gender, age, the date they took the test, and so on.

I don’t know how many sets of Test/Score values there may be. The users may have a combination of two or three, or there might be more. I have no problem setting a reasonable limit on the number of sets of values they use, but the whole point is, there will be more than one.

As you might guess, I’m new to Qlikview. I know this will use set analysis, but I’m unsure of the best way to go about it. If you have any ideas, please reply—thanks!

Re: Complicated selections--set analysis?

Sat, 01 Mar 2014 20:15:42 GMT

Can ur post ur datasample? then it might be easier to make.

Re: Complicated selections--set analysis?

Sat, 01 Mar 2014 20:20:14 GMT

I'm working on that now--thanks!

Re: Complicated selections--set analysis?

Sat, 01 Mar 2014 21:46:19 GMT

Here's the data.

Re: Complicated selections--set analysis?

Sat, 01 Mar 2014 21:55:12 GMT

Could u sum up what u want to be done? without wall of text Just in bulletpoints maybe?

Re: Complicated selections--set analysis?

Sat, 01 Mar 2014 22:04:55 GMT

OK--

For people who have these scores:

TEST01 1800 to 2000

TEST09 600 to 900

TEST11 over 200

What are their scores on TEST04?

Keeping in mind that tomorrow I'll want a whole different set of test names and score ranges.

Re: Re: Complicated selections--set analysis?

Sat, 01 Mar 2014 22:24:10 GMT

I have made this for TEST01 and TEST09 - Is it something like this?

Re: Re: Complicated selections--set analysis?

Sat, 01 Mar 2014 22:27:16 GMT

If you want the interval to be dynamic i suggest you make a table which maps to your testname and then updates with the rigth intervals.

And then you could update the scores in an excel ark or something like that.

But im not quite sure how to make it. Maybe someone else know

Re: Complicated selections--set analysis?

Sun, 02 Mar 2014 00:19:16 GMT

Thanks, Thomas, I'll take a look at this.

Re: Complicated selections--set analysis?

Mon, 03 Mar 2014 02:35:55 GMT

Thomas, I really appreciate you giving this a try! But I'm sorry, it's not quite what I had in mind. What I'm looking for is some way to make the query dynamic, allowing the users to choose which tests and what the ranges are on the fly. Also, I wasn't looking for an aggregation of the score, I was looking to find the set of people whose scores fit the criteria.

Anybody else have any ideas?

Re: Complicated selections--set analysis?

sebastiandperei — Mon, 03 Mar 2014 04:32:15 GMT

Hi.

See attached. First, in script, i need to use a Binary to run script, so you will need to move from line 3 to 10 at the end of your script.

It create two inputfields in testdata table.

Now, you need to create a tablebox with these fields, to let the user set the ranges of scores. Finally, use a pivot or sthaigt table to show the results.

Let me know if it was useful for you.

Re: Complicated selections--set analysis?

Mon, 03 Mar 2014 16:27:12 GMT

Thanks, Sebastian, I like the to-from fields added to the table. I'll work on this and see what I can do. I appreciate your help!

Re: Complicated selections--set analysis?

Thu, 06 Mar 2014 20:13:17 GMT

I've made some progress on this, but I've hit another sticking point and I hope somebody can help me. I can now identify the people who had scores in certain ranges. But what I ultimately want to do is to show the scores for another test for these people. I'm just using the initial set of tests to identify the people I want to look at. Please take a look at the attached--I'm sure set analysis can help me, but for the life of me I can't figure it out.

Thanks to all!!!!