Early bird tickets for Inspire 2023 are now available! Discounted pricing closes on January 31st. Save your spot!

Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer and Intelligence Suite.

Sort and group data

Chirag_Gandhi07
8 - Asteroid

Hi everyone, 

 

I have an excel file that contains data from three surveys I sent out. The first 16 columns contain the questions from survey 1. From column 17-34 contains the questions form survey 2, and columns 35-74 contains the questions from survey 3. The issue I am facing is that the rows (records) is not sorted to users who took survey 1, followed by surveys 2 and 3. It is all mixed up, so the first 10 may be users who took survey 1, row 11 may be survey 3 user, and row 15 may be a user who took survey 3. What I want to be able to do is to run through the data, and if there is a value in any field within the first 16 columns, then output that data to display "Survey 1 User". If there is a value in any field for columns 17-34 then output that data to display "Survey 2 user" and if there is a value in any field for columns 35-74, then display "Survey 3 User". Should I do this using a formula tool, or is there a better way? Also, i'm not entirely sure how to go about this using the formula tool. Any help would be much appreciated. Thanks!

4 REPLIES 4
Treyson
12 - Quasar
12 - Quasar

I am not 100% clear on the ask. Can you provide some sample data? Are you trying to create single records for a user who might have data in survey 1 and survey 3. Like, your data might currently have 2 records for user 1 who took surveys 1 and 3?

Treyson Marks
Senior Analytics Engineer
Chirag_Gandhi07
8 - Asteroid

So as you can see from the sample data I sent, Row 2 and 8 contains data for users who took survey 1, rows 3 & 5 contain data for users who took survey 2, and rows 4,6,and 7 contain data for users who took survey 3. I want to output the data into three different outputs. If there is a value in any field for columns Q1 to Q16, that should be output 1 for users who took survey 1. If there is a value in any field for columns Q17 to Q34, that should be output 2 for users who took survey 2. If there is a value in any field for columns Q35 to Q74, that should be output 3 for users who took survey 3. It's a little confusing but I hope my explanation helps!

danrh
13 - Pulsar

Lol. Seriously over-engineered my previous response. Just drop the following in a Formula tool to get the same result:

IF !IsNull([Q1]) THEN 'Survey 1 User'
ELSEIF !IsNull([Q17]) THEN 'Survey 2 User'
ELSE 'Survey 3 User' ENDIF

As long as only one survey is filled out on each record, this should be accurate.

Treyson
12 - Quasar
12 - Quasar

There are a bunch of ways to tackle this, but just given the response, I think that I would do something like the attached. It's dynamic but does require a knowledge of the range of questions for each survey. This would allow you to just change the ranges you want to assign to which survey and add surveys and ranges and the like.

 

Survey Ranges.png

Treyson Marks
Senior Analytics Engineer
Labels