Return multiple outputs based on common values in two different fields

[1997Mahendra]

Hello Alteryx Community, 

I need assistance with figuring out how to fix the following problem below.

This is the sample input data.

Need to get the data grouped with both Home and Host columns and return multiple output files based on the country names.

For example, there were 9 countries involved in this data, So I need 9 output files. And for Canada country output file, rows having Canada in both Home and Host columns must be there. So ideally, the output for canada file includes rows 2,3&8.

Likewise output for United states file must include rows 2,3,7,8,9&10 as all rows were having United States in either Home or Host columns.

I am attaching the sample data and the desired output in excel file.

Appreciate if you can let me know how this can be done in designer. Thanks for your time and help in advance.

[IraWatt]

Hey @1997Mahendra,

There's a few ways to do this do you want to output each country to a separate excel tab?

Easiest way to get your two outputs is just with a filter like this:

The OR will pass the row to the 'T' output if ether of the statements are true.

Any questions or issues please ask :)
HTH!
Ira

[grazitti_sapna]

Hi @1997Mahendra, another way of solving this issue is by concatenating the two fields as given below and then filtering out the results based on your requirement.

I hope this helps!

Thanks!

[1997Mahendra]

Hey @IraWatt, Thanks for your reply. But the actual data I'll input will have more than 50 countries and that changes every time with the file. So I need to get the output files only to countries that are involved in the file. Please if you can suggest considering this point.

[IraWatt · Accepted solution]

@1997Mahendra Here is an macro which can achieve this:

You see in the results a separate tab has been made for each country. The community has some great videos on Macros if you want to learn more. Please ask if any questions. 

[DataNath · Accepted solution]

Hey @1997Mahendra, this batch macro seems to handle this and should be dynamic, as you mention having so many possible countries. You just need to change the filepath and filename in the formula tool before the output and then it should run fine.

Also, when plugging in your real data set, just connect your full list of countries to the top (upside down ?) input of the macro, and your data set to the bottom one.

[1997Mahendra]

Sorry for the late reply. As I'm a beginner, It took time to learn the macros and implement that to my actual data file with the workflow you've given.

Thanks a lot @IraWatt for that. It's working as expected.

[1997Mahendra]

Thanks everyone for helping me here on this issue.