Need help with linking data and inputting information in a table

[MMManji]

Hey Alteryx Community, 

Really hoping you can help expand my *really basic* Alteryx knowledge. 

I have a wf that I'm working on and basically what i need it to do is:

-pull from multiple data sources > align column names as they vary> filter population through rules for "breaks"> create output file with those specific breaks of both data sources> also have a table summarizing breaks/total number for each field. 

I've attached an input file and what i would like the output result to look like. I would really appreciate any help you can offer. 

Thank you all! 

[Qiu]

@MMManji 
I think the tricky part is the "Column Name" as you have mentioned "align column names as they vary>".
Are these names having some naming convention or pattern we can use?
Ex: having postfix or prefix sort of
USA will be the postfix?

House Number

House NumberUSA

[MMManji]

@Qiu --yes, more often than not the field names will be a variation of the same word and might have a spacing issue or an added word. Would creating a table that shows alteryx the 1:1 mapping be helpful? 

[rfoster7]

so the field names change but do the number of columns and order of the columns remain consistent? As in if file one has 15 columns, do all the other files also have 15 columns? And if field "House Number" is in column 1 in the first file, does the corresponding field (called House NumberUSA or HN or H-num or whatever) also sit consistently in column 1? 

If the files have consistent numbers and orders of columns, you can bring them in without the field names and just merge them by field order then rename them to the correct field names in the workflow. 

If the files have inconsistent  numbers of columns and orders this gets a lot harder.

[Qiu]

@MMManji 
A mapping file would be helpful.
Please also provide sample data for input and desired output.