Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Returning values in order to a table

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.

UserAns_TextResponse_Level_2Ans_Minutes
8194CollabNoise15
8194CollabPrivacy15
8194DevelopNoise10
8194FocusNoise25
8194FocusPrivacy20
8194FocusQuiet Area20
8194NetworkNoise5

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.

UserFocus 1Focus 1 MinFocus 2Focus 2 MinFocus 3Focus 3 MinFocus 4Focus 4 MinFocus 5Focus 5 Min
8194Noise25Privacy20Quiet Area20-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

6 Replies
Not applicable

Re: Returning values in order to a table

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.

Not applicable

Re: Returning values in order to a table

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

MVP
MVP

Re: Returning values in order to a table

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.

See also attached.

Not applicable

Re: Returning values in order to a table

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

SortOrder.png

MVP
MVP

Re: Returning values in order to a table

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

....

Not applicable

Re: Returning values in order to a table

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

Community Browser