topic Re: Returning values in order to a table in QlikView

Returning values in order to a table

Sun, 12 May 2013 08:45:25 GMT

Hello, hope the community can help with this one.

I have a set of data/results from a survey with several users. I'm trying to crate a set of data by each user as the table below illustrates.

This is the source data of one of the users.

User	Ans_Text	Response_Level_2	Ans_Minutes
8194	Collab	Noise	15
8194	Collab	Privacy	15
8194	Develop	Noise	10
8194	Focus	Noise	25
8194	Focus	Privacy	20
8194	Focus	Quiet Area	20
8194	Network	Noise	5

I would like to create a table that takes the USER as the dimension and where Ans_Text='Focus' return the name in Response_Level_2 and then return the value in Ans_Minutes so that it looks like this in the table. Ideally to have the resulting table in order highest result first.

User	Focus 1	Focus 1 Min	Focus 2	Focus 2 Min	Focus 3	Focus 3 Min	Focus 4	Focus 4 Min	Focus 5	Focus 5 Min
8194	Noise	25	Privacy	20	Quiet Area	20	-	0	-	0

In some cases there will be a User with 5 results for the Ans_Text response of Focus so when there is less I want to return a - value or 0 for the items in the table.

I hope someone can help

Thanks

Rob

Re: Returning values in order to a table

Sun, 12 May 2013 10:55:51 GMT

Hi Rob,

I have a drafted a test application which will meet your requirements. The only thing is the expressions are all hard coded making the table static and not dynamic. Are there only 5 distinct responses for the focus Ans_text? If so you can as in my example hard code these responses as expressions within a straight table.

See my example for more details. You will need to modify the last 4 expressions of the chart to change the 'Area x' to the correct response.

KR Matt.

Re: Returning values in order to a table

Sun, 12 May 2013 12:04:32 GMT

Hi Matt, answers to your questions.

The table I'm trying to create has all hard coded expression - it is a set format we want to produce on many surveys we have.

There are more that 5 possible answers for the focus Ans_text - something like 28 possibilities and that's whay I was wondering how I can take the top 5 values and there mins associated with the category?

So I'm thinking each expression for e.g Focus 1, will need to pull the first response based on the highest value of mins then Focus 2 to pull the second response based on the second highest value of mins and so on.

Hope that makes sense!

Your help is appreciated.

Thanks

Rob

Re: Returning values in order to a table

swuehl — Sun, 12 May 2013 12:32:32 GMT

Rob,

you probably can use FirstSortedValue() for this, like

=FirstSortedValue( {<Ans_Text= {Focus}>} Response_Level_2, -SortOrder, 1)

and

=FirstSortedValue( {<Ans_Text= {Focus}>} Ans_Minutes, -SortOrder, 1)

to retrieve the Level_2 and minutes for the top answer.

I introduced a SortOrder in the load to handle the cases where your minutes are identical.

Re: Returning values in order to a table

Mon, 13 May 2013 12:38:44 GMT

Hi swuehi, I tried the expression but the -SortOrder keeps giving a "Bad Field Name(s)" result

Re: Returning values in order to a table

swuehl — Mon, 13 May 2013 12:44:30 GMT

Strange, I assume you also introduced the field in the load as indicated?

LOAD *, Ans_Minutes+1/(RecNo()+10) as SortOrder INLINE [

User, Ans_Text, Response_Level_2, Ans_Minutes

8194, Collab, Noise, 15

8194, Collab, Privacy, 15

....

Re: Returning values in order to a table

Fri, 17 May 2013 08:10:13 GMT

Hi sorry been on other things.

Going to give this a go today as I have not added this to the load!!

Will let you both know.

Thanks

Rob