What would be a good option for adding a 1 or 0 to a column based on answers in another

Question

I wasnt sure how to ask this question so sorry if the title is weird.

Basically I used alteryx to combine 5 reports on some online classes people took.

Then the person requesting this added an extra bit to the request, they sent another file that has 5 columns for the 5 courses, with a 1 in classes that the person attended and nothing in the courses they didnt attend.

Basically I want to take the main file and do a join or union or something that amounts to if What is in Column B matches the header of columns AG-AK then put a 1 in that row.

Kenda · Answer

Hey @rmartori

I'll try again with this new information, although my approach is largely the same.

With your second file, first Transpose it, grouping by your first three fields. This will put each of the class names in rows under the field name "Name."

Then use a join to join the two files together using email from both sides and Event Title from the left and Name from the right. This will align each email with each course listed in both the left and right. Note, the class names must match exactly for this to work. From here, you can change the Duration field from the left to be called Value. Then last you will Cross Tab the fields from the right side back so that there is one row per email.

Only one match occurred here because the Contacts fields were not named consistently between the two sources.

Workflow.yxmd

rmartori · Answer

Is there anything like excel countifs function in alteryx?

Essentially I need it to be something like If B equals "Accounting 101" and F Equals [Matching email from another sheet] then AG equals 1.

Not sure how great this example below will be but the two data sources would look like.
The below are columns A-F there are more but they wont matter for what I am trying to do.

Event IdEvent TitleFirst NameLast NameTitleEmail1378279Contacts and Leads (Part 2)RobRobertsonManageremail@fakeemail.com1378279Contacts and Leads (Part 1)FredAstaireSenior ManagerFakeemail@email.com1378279Mobile AppGrouchoMarxManagerDitto@Imnotcreative.com

The second file: While this is technically A-AK I deleted all but the important parts And most columns in the below file I plan to replace with files from the above file(things like duration department, etc.)

Email - Primary Work WorkerContacts & Leads - 1Contacts & Leads - 2Mobile App email@fakeemail.com1Rob Robertson    Fakeemail@email.com1Fred Astair    Ditto@Imnotcreative.com1Groucho Marx111

Actually thinking about it The top file has a column(L) that is for duration watched of the Event.

So if possible What I would like to do is and hopefully I explain this right and it isnt too complicated.:

Take Column A from the second file and search the first file for a matching email, when it finds that email it then checks Column B. If it finds a Course that matches one of the headers in the second file It puts the total Duration from Column L in that cell and moves on.

So for example I want to search the first document for email@fakeemail.com, when it finds it check column B in this case it would see "Contacts and Leads (Part 2)" it would then take the Duration from Column L and put it in the second file under the column header Contacts & Leads - 2/

Is this even possible?

Kenda · Answer

Hey @rmartori

I'm not sure if I have exactly addressed your issue here, but please take a look at the attached and see if it starts you in the right direction. If it does not, please provide sample data to show what your data looks like now and what you would like it to look like in the end.

New Workflow5.yxmd