community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

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

Need to add duplicate rows based on a few conditions

Highlighted
Meteor

Hello Team,

 

I have an ask to map certain values in rows to values across in 4 different columns based a few conditions.

Based on the column Course method : I need to map people to different categories as per the attached mapping sheet and add them as different rows

 

For example - below is the current look

SL no  User ID Course Method                                 ParticipantCredit LeaderCredit LeaderAPCredit AuthorCredit

1          3455     Classroom Learning (Instructor-led)       15                      0                          1                   2

 

I need to be able to generate 3 rows one below the other for the above person, as he has values more than 0 in 3

different columns, and while generating 3 rows, we need to map them to LEARNER_PHYSICALCLASSROOM, INSTRUCTORAP_PHYSICALCLASSROOM and AUTHOR_PHYSICALCLASSROOM in a column called status, with all the other values being the same in the row., we would not need the last 4 columns once we do this.

 

Like wise, we need to have as many new rows added for the same user as and when he has values more than 0 in  the last 4 columns, 

Data MApping

 

Course MethodParticipantLeaderLeader APAuthor
Web-Based Learning (Self-study)LEARNER_ELEARNINGN/AN/AAUTHOR_ELEARNING
 LEARNER_ELEARNINGN/AN/AAUTHOR_ELEARNING
Distance Learning (Instructor-led)LEARNER_VIRTUALOTHERINSTRUCTED_VIRTUALOTHERINSTRUCTORAP_VIRTUALOTHERAUTHOR_VIRTUALOTHER
Classroom Learning (Instructor-led)LEARNER_PHYSICALCLASSROOMINSTRUCTED_PHYSICALCLASSROOMINSTRUCTORAP_PHYSICALCLASSROOMAUTHOR_PHYSICALCLASSROOM
Virtual Learning (Instructor-led)LEARNER_VIRTUALCLASSROOMINSTRUCTED_VIRTUALCLASSROOMINSTRUCTORAP_VIRTUALCLASSROOMAUTHOR_VIRTUALCLASSROOM
ConferenceLEARNER_PHYSICALCLASSROOMINSTRUCTED_PHYSICALCLASSROOMINSTRUCTORAP_PHYSICALCLASSROOMAUTHOR_PHYSICALCLASSROOM

 

1) If he has one  value of more than 0 across 4 columns, no need to add a row, it would be just to add a new column and map it to any one of the above said status

2) if he has 2 values of more than one, add one row, with the first row to be mapped to the status name and second row as well to another.

3) and so on.

 

attaching the mapping and a sample data set. Please help.

Asteroid

One way to do this is by using the transpose tool to map the last 4 columns to their corresponding status using course method and the column names. The final step filters out any rows with values = 0. Hope this helps!

Capture.JPG

Aurora

hi @Kaushik 

 

Similar to @acastelazo, adding in a few extra tools to preserve the sort order of the original data

 

wf.png

 

Dan

Labels