Hi everyone,
I have a column (Software Title) that contains software titles and another column (Q4) that contains the values yes or no based on email addresses. I would like to filter out any software title that appears more than once and has yes and no for (Q4). Below is an example;
Email Q4 Software Title
test@test.com Yes Tableau
test1@test.com No Tableau
test2@test.com Yes Alteryx
test3@test.com No Visual Studio
So the filtered data would be:
Email Q4 Software Title
test@test.com Yes Tableau
test1@test.com No Tableau
(The filtered data is as is because Tableau shows up twice AND has both yes and no for Q4). Any help on how this can be achieved would be much appreciated. Thanks!
Solved! Go to Solution.
@KendaI thought it would work too but it only displays the software titles with one yes and one no. So for example: Tableau with emails test@test.com and test2@test.com with yes for test and no for test2 would display but if I added a third, test3@test.com that has software title Tableau with yes for Q4, then the software title Tableau doesn't display. It only displays if there are two records with yes and no not if there are more than two with yes, yes, no, or any other combination. Does that make better sense?
@Chirag_Gandhi07 I see what you are saying, but I'm not sure why you are getting that output. As shown in the screenshots in my last post (copied below), there are four records for Tableau (Yes, No, Yes, unrespondent) which all appear in the output. Only the Yes and No Q4 values have the word split, as shown in records 1, 5, and 6 of the output. There are 8 records from the Input and 8 records that come out the Browse. Are you changing some of the configuration settings after downloading the workflow? Download the attached workflow and run it without making any adjustments. Are you seeing the same input and output?
@KendaYes I am. Not sure what's going on with my workflow. Anyway, thank you so much for your help! Really appreciate it!
@Chirag_Gandhi07 Is the output photo from my last post what you are looking for?
If you downloaded my very last workflow and tried running that as-is and are still not getting the output I posted as a screenshot, that would be very weird. If this is what you did and the screenshot I posted is what you're looking for in your output, try attaching your workflow here, and I can try running that to see if I get the same output as you. Please also attach a screenshot of the output you're seeing so that I can check it against mine after running.
@KendaI think I found the issue. Since the three options for Q4 are Yes, No, and Unrespondent, when I group by both Software Title and Q4, it only shows once per those three options. So I am looking at my original data and there are five different emails, with the same Software Title, and Yes, Yes, No, Unrespondent, and Unrespondent for Q4. When I use the summarize tool, rather than having five outputs, it only has three. One for Yes, one for No, and one for Unrespondent. Not sure why it is doing that.
@KendaNo I understand. Looking at the data more closely, the software titles with Yes, No, and Unrespondent for Q4 are not displaying Split for the Yes and No rows. I have no idea why but that is the issue. Thanks for your help!
@KendaI have attached your workflow and I added some data. For the software title Visual Basic, split is only being displayed for Yes and Unrespondent, and not for No. Also, in my real data some software titles may only have Yes for Q4 and some may have all three options, some may just have Yes and No, and some could have 5 No's. It all depends.
Thank you for attaching the workflow with your data. I am finally able to see the discrepancy. The reason we were seeing different things is because your Unrespondent was capitalized and mine was lower case. This caused the Summarize to sort the Q4 column differently.
Change your Multi-Field Formula expression to this:
iif(([Q4]!=[Row+1:Q4]) && ([Row-1:Q4]!=[Q4]) && (!isempty([Row-1:Q4]) || !isempty([Row+1:Q4])) && (([Row-2:Q4]="No" || [Row-1:Q4]="No" || [Q4]="No") && ([Row+2:Q4]="Yes" || [Row+1:Q4]="Yes" || [Q4]="Yes") && ([Q4]!="Unrespondent")),"Split",null())
This should get you the output you're looking for.
@KendaThank you so much!!!