Using Alteryx to format data import files

[chrislarson]

I am exploring using Alteryx to help format data from a template clients fill out.  The process involves client's wanting to take their historical data from a competitor and migrate it into our solution.  This requires them to fill out a one page data template in Excel with several columns.  From here, our data analysts prep the import files - and this involves creating 5 separate import files for the 5 separate tables that the data will be inserted into within our database.  I am trying to find a tool that will look at this data, then parse out the data into each of the 5 tables needed with just the fields needed for each of the files.  For example, this is giving data for NPO grants and the data template the client fills out is created at the grant level (each row corresponds to one grant).  And one of the files we generate for import from this template is an organization file.  This file will have a unique ID, org name, and demographic info.  I am looking for a tool that will spilt out just the unique organization records by ID and include them in a separate file with just the organization demographic info.  I have tried using the Table, Unique, and Filter tools - but none seem to get me to where I need to be.  If anyone has any suggestions, I would love to hear them!

[jrlindem]

Do you have an example of a few of the values and what the expected outcome would look like?

But if i'm reading your need correctly, you just need to leverage batch reports.  Meaning, you use the Record ID to create a unique output file name and each associated record writes to that respective file...

If this is in the ballpark of your need, let me know and I can try and help more specifically.  -Jay

[chrislarson]

Here are a few of the headers on the template: 

Application Import ID

Program Name

Workflow Name

Workflow Level Name

Application Status ID

Submitted Date

Organization Import ID

Organization Name

Organization Registration Number

Organization Address

The 1st 6 headers are related to the grant record specifically, while the last 4 are related to the organization that requested the grant. In this data, there are several grants for the same organizations (overall there are about 3500 grants and a total of 1200 unique organizations that money was granted to.  My goal is to use the import file to create an output file for just unique organizations and just the organization header fields.  So using the example headers above, the desired output file would be only the last 4 columns and only rows for each unique Organization Import ID within the entire dataset.

[jrlindem]

Take a peek at this previous discussion article where they talk about generating multiple outputs based on data from within the workflow:

Solved: Generate multiple output from 2 fields? - Alteryx Community

Is this along the lines of what you're attempting to do?  -Jay

[chrislarson]

So I gave this a shot, but this does not seem to be it.  Forgive me, but I am a brand new user here and I did search extensively for a solution before posting.  Basically I need to create multiple files from the same dataset.  Think of the main dataset as a relational database, and I need to figure out how to split out the separate database tables using a workflow in Alteryx.  For reference, this is something I normally do manually using Excel.  For the Organization example I used above, I would take just the organization columns and copy and paste them into a new sheet.  Then I would use the remove duplicates function and remove all duplicates by Organization Import ID - leaving me with just a table of all unique organizations in the data.  This is the output I am looking for Alteryx to help me achieve via automation.

[jrlindem]

@chrislarson 

No worries and welcome to using Alteryx!  I believe I have a sense of what you're after.  Let me mock something up and I'll circle back around.  -Jay

[jrlindem · Accepted solution]

@chrislarson 

Okay, give this a peek.  I built this example out in a way that you can see how one or many files are created based on the data itself.  You can add/remove fields based on your needs using the Select Tools.  Pay special attention to how I'm building dynamic filepaths using the Formula Tool- specifying path, filename, and sheet name dynamically.  Then in the Output Tools, it uses that instruction to generate one or many outputs.

If you need to remove some fields and roll up the distinction, you can add in a Unique Tool where needed.

As for what data and summary you want, you can adapt from there!  Hope this helps.  Workflow I attached.  Currently the outputs will save to the same location where the workflow is saved.  So just keep that in mind.

Cheers, -Jay

[chrislarson · Accepted solution]

This is great - thank you!  I replicated what you did within my own workflow and it seems to be pretty close to what I need.  I didn't need the batch report files - instead I will actually be creating different tables from different portions of the whole dataset. But the Organization Summary report is exactly what I need (minus the program count column at the end).  The only issue still remaining is that it is not filtering out duplicates properly.  This may be due to my data and the fact I have a total of 10 organization columns I am including in my summary report and that the data isn't super clean to begin with.  For example, I have one organization with Organization Import ID 442 that appears 7 times in the overall dataset, but it was included twice on my summary report due to small variations in the address fields I included.  Is there a way to have the end result filter out duplicates that are evaluated by only on 2 columns (instead of all 10)?  Ideally I would like to just have all unique organization records with the duplicate evaluation on the ID and Name fields only.

[jrlindem]

Yes, that is a little bit tricky right because the address differences are what's creating non-uniqueness.  You could remove the address, roll up the rest of the data to the distinct records and then use the FIND REPLACE tool to append one of the address derivatives back to the summarized data.  This tool works like a VLOOKUP.  So you'd need to make sure that your addresses still had the Organization to match back to, but it would just grab the first match and dump the rest (as opposed to using the JOIN tool which would create a cartesian).

Regarding the batch reports, if you use Alteryx long enough that capability will become very valuable.  So hold onto that example for the future!

Glad I could help on this.  -Jay