Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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
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.
Hi swuehi, I tried the expression but the -SortOrder keeps giving a "Bad Field Name(s)" result
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
....
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