Need help to clean this dataset please :)

[rively90]

i all,

I'm quite new to Alteryx and would like to know if someone can help guide me or provide examples to clean this dataset. This data is too hard to me to clean as I'm just a beginner.

I know that I need to combine all of the data from 2014 to 2021 into one. Should I pivot the data and do Vlook up? What should I do? Thank you for your time!

https://tinyurl.com/eeocdataset

Downloads

• Download 2021 EEO-1 Public Use File (PUF)
• Download 2020 EEO-1 Public Use File (PUF)
• Download 2019 EEO-1 Public Use File (PUF)
• Download 2018 EEO-1 Public Use File (PUF)
• Download 2017 EEO-1 Public Use File (PUF)
• Download 2016 EEO-1 Public Use File (PUF)
• Download 2015 EEO-1 Public Use File (PUF)
• Download 2014 EEO-1 Public Use File (PUF)

There are also some labels under page 6-12

• PUF User Guide
  
  Appendix 1 Variable Labels for the PUF Datasets
  Column Name Column Label
  NATION Label for National Level Geography
  REGION Label for Region Level Geography
  DIVISION Label for Division Level Geography
  STATE Label for State Level Geography
  CBSA Label for CBSA Level Geography
  COUNTY Label for County Level Geography
  NAICS2 Two-Digits Industry Code (Sector)
  NAICS2_Name Label for Two-Digits Industry Code
  NAICS3 Three-Digits Industry Codes (Subsector)
  NAICS3_Name Label for Three-Digits Industry Code
  Establishments Count of Establishments
  TOTAL10 Race: Aggregate, Sex: Aggregate, Job: Aggregate
  WHT10 Race: White, Sex: Aggregate, Job: Aggregate
  BLKT10 Race: Black or African American, Sex: Aggregate, Job: Aggregate
  HISPT10 Race: Hispanic, Sex: Aggregate, Job: Aggregate
  ASIANT10 Race: Asian, Sex: Aggregate, Job: Aggregate
  AIANT10 Race: American Indian or Alaska Native, Sex: Aggregate, Job: Aggregate
  NHOPIT10 Race: Native Hawaiian or Other Pacific Islander, Sex: Aggregate, Job: Aggregate
  TOMRT10 Race: Two or more Races, Sex: Aggregate, Job: Aggregate
  MT10 Race: Aggregate, Sex: Male, Job: Aggregate
  FT10 Race: Aggregate, Sex: Female, Job: Aggregate
  TOTAL1 Race: Aggregate, Sex: Aggregate, Job: Senior Off and Managers
  TOTAL2 Race: Aggregate, Sex: Aggregate, Job: Professionals
  TOTAL3 Race: Aggregate, Sex: Aggregate, Job: Technicians
  TOTAL4 Race: Aggregate, Sex: Aggregate, Job: Sales Workers
  TOTAL5 Race: Aggregate, Sex: Aggregate, Job: Clericals
  TOTAL6 Race: Aggregate, Sex: Aggregate, Job: Craft
  TOTAL7 Race: Aggregate, Sex: Aggregate, Job: Operatives
  TOTAL8 Race: Aggregate, Sex: Aggregate, Job: Labors
  TOTAL9 Race: Aggregate, Sex: Aggregate, Job: Service
  TOTAL1_2 Race: Aggregate, Sex: Aggregate, Job: Mid Off and Managers
  WHM10 Race: White, Sex: Male, Job: Aggregate
  BLKM10 Race: Black or African American, Sex: Male, Job: Aggregate
  HISPM10 Race: Hispanic, Sex: Male, Job: Aggregate
  ASIANM10 Race: Asian, Sex: Male, Job: Aggregate
  AIANM10 Race: American Indian or Alaska Native, Sex: Male, Job: Aggregate
  NHOPIM10 Race: Native Hawaiian or Other Pacific Islander, Sex: Male, Job: Aggregate
  NORC | The Equal Employment Opportunity Commission (EEOC)
  7
  TOMRM10 Race: Two or more Races, Sex: Male, Job: Aggregate
  WHF10 Race: White, Sex: Female, Job: Aggregate
  BLKF10 Race: Black or African American, Sex: Female, Job: Aggregate
  HISPF10 Race: Hispanic, Sex: Female, Job: Aggregate
  ASIANF10 Race: Asian, Sex: Female, Job: Aggregate
  AIANF10 Race: American Indian or Alaska Native, Sex: Female, Job: Aggregate
  NHOPIF10 Race: Native Hawaiian or Other Pacific Islander, Sex: Female, Job: Aggregate
  TOMRF10 Race: Two or more Races, Sex: Female, Job: Aggregate
  WHT1 Race: White, Sex: Aggregate, Job: Senior Off and Managers
  WHT2 Race: White, Sex: Aggregate, Job: Professionals
  WHT3 Race: White, Sex: Aggregate, Job: Technicians
  WHT4 Race: White, Sex: Aggregate, Job: Sales Workers
  WHT5 Race: White, Sex: Aggregate, Job: Clericals
  WHT6 Race: White, Sex: Aggregate, Job: Craft
  WHT7 Race: White, Sex: Aggregate, Job: Operatives
  WHT8 Race: White, Sex: Aggregate, Job: Labors
  WHT9 Race: White, Sex: Aggregate, Job: Service
  WHT1_2 Race: White, Sex: Aggregate, Job: Mid Off and Managers
  BLKT1 Race: Black or African American, Sex: Aggregate, Job: Senior Off and Managers
  BLKT2 Race: Black or African American, Sex: Aggregate, Job: Professionals
  BLKT3 Race: Black or African American, Sex: Aggregate, Job: Technicians
  BLKT4 Race: Black or African American, Sex: Aggregate, Job: Sales Workers
  BLKT5 Race: Black or African American, Sex: Aggregate, Job: Clericals
  BLKT6 Race: Black or African American, Sex: Aggregate, Job: Craft
  BLKT7 Race: Black or African American, Sex: Aggregate, Job: Operatives
  BLKT8 Race: Black or African American, Sex: Aggregate, Job: Labors
  BLKT9 Race: Black or African American, Sex: Aggregate, Job: Service
  BLKT1_2 Race: Black or African American, Sex: Aggregate, Job: Mid Off and Managers
  HISPT1 Race: Hispanic, Sex: Aggregate, Job: Senior Off and Managers
  HISPT2 Race: Hispanic, Sex: Aggregate, Job: Professionals
  HISPT3 Race: Hispanic, Sex: Aggregate, Job: Technicians
  HISPT4 Race: Hispanic, Sex: Aggregate, Job: Sales Workers
  HISPT5 Race: Hispanic, Sex: Aggregate, Job: Clericals
  HISPT6 Race: Hispanic, Sex: Aggregate, Job: Craft
  HISPT7 Race: Hispanic, Sex: Aggregate, Job: Operatives
  HISPT8 Race: Hispanic, Sex: Aggregate, Job: Labors
  HISPT9 Race: Hispanic, Sex: Aggregate, Job: Service
  HISPT1_2 Race: Hispanic, Sex: Aggregate, Job: Mid Off and Managers
  ASIANT1 Race: Asian, Sex: Aggregate, Job: Senior Off and Managers
  ASIANT2 Race: Asian, Sex: Aggregate, Job: Professionals
  ASIANT3 Race: Asian, Sex: Aggregate, Job: Technicians
  NORC | The Equal Employment Opportunity Commission (EEOC)
  8
  ASIANT4 Race: Asian, Sex: Aggregate, Job: Sales Workers
  ASIANT5 Race: Asian, Sex: Aggregate, Job: Clericals
  ASIANT6 Race: Asian, Sex: Aggregate, Job: Craft
  ASIANT7 Race: Asian, Sex: Aggregate, Job: Operatives
  ASIANT8 Race: Asian, Sex: Aggregate, Job: Labors
  ASIANT9 Race: Asian, Sex: Aggregate, Job: Service
  ASIANT1_2 Race: Asian, Sex: Aggregate, Job: Mid Off and Managers
  AIANT1 Race: American Indian or Alaska Native, Sex: Aggregate, Job: Senior Off and Managers
  AIANT2 Race: American Indian or Alaska Native, Sex: Aggregate, Job: Professionals
  AIANT3 Race: American Indian or Alaska Native, Sex: Aggregate, Job: Technicians
  AIANT4 Race: American Indian or Alaska Native, Sex: Aggregate, Job: Sales Workers
  AIANT5 Race: American Indian or Alaska Native, Sex: Aggregate, Job: Clericals
  AIANT6 Race: American Indian or Alaska Native, Sex: Aggregate, Job: Craft
  AIANT7 Race: American Indian or Alaska Native, Sex: Aggregate, Job: Operatives
  AIANT8 Race: American Indian or Alaska Native, Sex: Aggregate, Job: Labors
  AIANT9 Race: American Indian or Alaska Native, Sex: Aggregate, Job: Service
  AIANT1_2 Race: American Indian or Alaska Native, Sex: Aggregate, Job: Mid Off and Managers
  NHOPIT1 Race: Native Hawaiian or Other Pacific Islander, Sex: Aggregate, Job: Senior Off and Managers
  NHOPIT2 Race: Native Hawaiian or Other Pacific Islander, Sex: Aggregate, Job: Professionals
  NHOPIT3 Race: Native Hawaiian or Other Pacific Islander, Sex: Aggregate, Job: Technicians
  NHOPIT4 Race: Native Hawaiian or Other Pacific Islander, Sex: Aggregate, Job: Sales Workers
  NHOPIT5 Race: Native Hawaiian or Other Pacific Islander, Sex: Aggregate, Job: Clericals
  NHOPIT6 Race: Native Hawaiian or Other Pacific Islander, Sex: Aggregate, Job: Craft
  NHOPIT7 Race: Native Hawaiian or Other Pacific Islander, Sex: Aggregate, Job: Operatives
  NHOPIT8 Race: Native Hawaiian or Other Pacific Islander, Sex: Aggregate, Job: Labors
  NHOPIT9 Race: Native Hawaiian or Other Pacific Islander, Sex: Aggregate, Job: Service
  NHOPIT1_2 Race: Native Hawaiian or Other Pacific Islander, Sex: Aggregate, Job: Mid Off and Managers
  TOMRT1 Race: Two or more Races, Sex: Aggregate, Job: Senior Off and Managers
  TOMRT2 Race: Two or more Races, Sex: Aggregate, Job: Professionals
  TOMRT3 Race: Two or more Races, Sex: Aggregate, Job: Technicians
  TOMRT4 Race: Two or more Races, Sex: Aggregate, Job: Sales Workers
  TOMRT5 Race: Two or more Races, Sex: Aggregate, Job: Clericals
  TOMRT6 Race: Two or more Races, Sex: Aggregate, Job: Craft
  TOMRT7 Race: Two or more Races, Sex: Aggregate, Job: Operatives
  TOMRT8 Race: Two or more Races, Sex: Aggregate, Job: Labors
  TOMRT9 Race: Two or more Races, Sex: Aggregate, Job: Service
  TOMRT1_2 Race: Two or more Races, Sex: Aggregate, Job: Mid Off and Managers
  MT1 Race: Aggregate, Sex: Male, Job: Senior Off and Managers
  MT2 Race: Aggregate, Sex: Male, Job: Professionals
  MT3 Race: Aggregate, Sex: Male, Job: Technicians
  MT4 Race: Aggregate, Sex: Male, Job: Sales Workers
  NORC | The Equal Employment Opportunity Commission (EEOC)
  9
  MT5 Race: Aggregate, Sex: Male, Job: Clericals
  MT6 Race: Aggregate, Sex: Male, Job: Craft
  MT7 Race: Aggregate, Sex: Male, Job: Operatives
  MT8 Race: Aggregate, Sex: Male, Job: Labors
  MT9 Race: Aggregate, Sex: Male, Job: Service
  MT1_2 Race: Aggregate, Sex: Male, Job: Mid Off and Managers
  FT1 Race: Aggregate, Sex: Female, Job: Senior Off and Managers
  FT2 Race: Aggregate, Sex: Female, Job: Professionals
  FT3 Race: Aggregate, Sex: Female, Job: Technicians
  FT4 Race: Aggregate, Sex: Female, Job: Sales Workers
  FT5 Race: Aggregate, Sex: Female, Job: Clericals
  FT6 Race: Aggregate, Sex: Female, Job: Craft
  FT7 Race: Aggregate, Sex: Female, Job: Operatives
  FT8 Race: Aggregate, Sex: Female, Job: Labors
  FT9 Race: Aggregate, Sex: Female, Job: Service
  FT1_2 Race: Aggregate, Sex: Female, Job: Mid Off and Managers
  WHM1 Race: White, Sex: Male, Job: Senior Off and Managers
  BLKM1 Race: Black or African American, Sex: Male, Job: Senior Off and Managers
  HISPM1 Race: Hispanic, Sex: Male, Job: Senior Off and Managers
  ASIANM1 Race: Asian, Sex: Male, Job: Senior Off and Managers
  AIANM1 Race: American Indian or Alaska Native, Sex: Male, Job: Senior Off and Managers
  NHOPIM1 Race: Native Hawaiian or Other Pacific Islander, Sex: Male, Job: Senior Off and Managers
  TOMRM1 Race: Two or more Races, Sex: Male, Job: Senior Off and Managers
  WHF1 Race: White, Sex: Female, Job: Senior Off and Managers
  BLKF1 Race: Black or African American, Sex: Female, Job: Senior Off and Managers
  HISPF1 Race: Hispanic, Sex: Female, Job: Senior Off and Managers
  ASIANF1 Race: Asian, Sex: Female, Job: Senior Off and Managers
  AIANF1 Race: American Indian or Alaska Native, Sex: Female, Job: Senior Off and Managers
  NHOPIF1 Race: Native Hawaiian or Other Pacific Islander, Sex: Female, Job: Senior Off and Managers
  TOMRF1 Race: Two or more Races, Sex: Female, Job: Senior Off and Managers
  WHM2 Race: White, Sex: Male, Job: Professionals
  BLKM2 Race: Black or African American, Sex: Male, Job: Professionals
  HISPM2 Race: Hispanic, Sex: Male, Job: Professionals
  ASIANM2 Race: Asian, Sex: Male, Job: Professionals
  AIANM2 Race: American Indian or Alaska Native, Sex: Male, Job: Professionals
  NHOPIM2 Race: Native Hawaiian or Other Pacific Islander, Sex: Male, Job: Professionals
  TOMRM2 Race: Two or more Races, Sex: Male, Job: Professionals
  WHF2 Race: White, Sex: Female, Job: Professionals
  BLKF2 Race: Black or African American, Sex: Female, Job: Professionals
  HISPF2 Race: Hispanic, Sex: Female, Job: Professionals
  ASIANF2 Race: Asian, Sex: Female, Job: Professionals
  NORC | The Equal Employment Opportunity Commission (EEOC)
  10
  AIANF2 Race: American Indian or Alaska Native, Sex: Female, Job: Professionals
  NHOPIF2 Race: Native Hawaiian or Other Pacific Islander, Sex: Female, Job: Professionals
  TOMRF2 Race: Two or more Races, Sex: Female, Job: Professionals
  WHM3 Race: White, Sex: Male, Job: Technicians
  BLKM3 Race: Black or African American, Sex: Male, Job: Technicians
  HISPM3 Race: Hispanic, Sex: Male, Job: Technicians
  ASIANM3 Race: Asian, Sex: Male, Job: Technicians
  AIANM3 Race: American Indian or Alaska Native, Sex: Male, Job: Technicians
  NHOPIM3 Race: Native Hawaiian or Other Pacific Islander, Sex: Male, Job: Technicians
  TOMRM3 Race: Two or more Races, Sex: Male, Job: Technicians
  WHF3 Race: White, Sex: Female, Job: Technicians
  BLKF3 Race: Black or African American, Sex: Female, Job: Technicians
  HISPF3 Race: Hispanic, Sex: Female, Job: Technicians
  ASIANF3 Race: Asian, Sex: Female, Job: Technicians
  AIANF3 Race: American Indian or Alaska Native, Sex: Female, Job: Technicians
  NHOPIF3 Race: Native Hawaiian or Other Pacific Islander, Sex: Female, Job: Technicians
  TOMRF3 Race: Two or more Races, Sex: Female, Job: Technicians
  WHM4 Race: White, Sex: Male, Job: Sales Workers
  BLKM4 Race: Black or African American, Sex: Male, Job: Sales Workers
  HISPM4 Race: Hispanic, Sex: Male, Job: Sales Workers
  ASIANM4 Race: Asian, Sex: Male, Job: Sales Workers
  AIANM4 Race: American Indian or Alaska Native, Sex: Male, Job: Sales Workers
  NHOPIM4 Race: Native Hawaiian or Other Pacific Islander, Sex: Male, Job: Sales Workers
  TOMRM4 Race: Two or more Races, Sex: Male, Job: Sales Workers
  WHF4 Race: White, Sex: Female, Job: Sales Workers
  BLKF4 Race: Black or African American, Sex: Female, Job: Sales Workers
  HISPF4 Race: Hispanic, Sex: Female, Job: Sales Workers
  ASIANF4 Race: Asian, Sex: Female, Job: Sales Workers
  AIANF4 Race: American Indian or Alaska Native, Sex: Female, Job: Sales Workers
  NHOPIF4 Race: Native Hawaiian or Other Pacific Islander, Sex: Female, Job: Sales Workers
  TOMRF4 Race: Two or more Races, Sex: Female, Job: Sales Workers
  WHM5 Race: White, Sex: Male, Job: Clericals
  BLKM5 Race: Black or African American, Sex: Male, Job: Clericals
  HISPM5 Race: Hispanic, Sex: Male, Job: Clericals
  ASIANM5 Race: Asian, Sex: Male, Job: Clericals
  AIANM5 Race: American Indian or Alaska Native, Sex: Male, Job: Clericals
  NHOPIM5 Race: Native Hawaiian or Other Pacific Islander, Sex: Male, Job: Clericals
  TOMRM5 Race: Two or more Races, Sex: Male, Job: Clericals
  WHF5 Race: White, Sex: Female, Job: Clericals
  BLKF5 Race: Black or African American, Sex: Female, Job: Clericals
  HISPF5 Race: Hispanic, Sex: Female, Job: Clericals
  NORC | The Equal Employment Opportunity Commission (EEOC)
  11
  ASIANF5 Race: Asian, Sex: Female, Job: Clericals
  AIANF5 Race: American Indian or Alaska Native, Sex: Female, Job: Clericals
  NHOPIF5 Race: Native Hawaiian or Other Pacific Islander, Sex: Female, Job: Clericals
  TOMRF5 Race: Two or more Races, Sex: Female, Job: Clericals
  WHM6 Race: White, Sex: Male, Job: Craft
  BLKM6 Race: Black or African American, Sex: Male, Job: Craft
  HISPM6 Race: Hispanic, Sex: Male, Job: Craft
  ASIANM6 Race: Asian, Sex: Male, Job: Craft
  AIANM6 Race: American Indian or Alaska Native, Sex: Male, Job: Craft
  NHOPIM6 Race: Native Hawaiian or Other Pacific Islander, Sex: Male, Job: Craft
  TOMRM6 Race: Two or more Races, Sex: Male, Job: Craft
  WHF6 Race: White, Sex: Female, Job: Craft
  BLKF6 Race: Black or African American, Sex: Female, Job: Craft
  HISPF6 Race: Hispanic, Sex: Female, Job: Craft
  ASIANF6 Race: Asian, Sex: Female, Job: Craft
  AIANF6 Race: American Indian or Alaska Native, Sex: Female, Job: Craft
  NHOPIF6 Race: Native Hawaiian or Other Pacific Islander, Sex: Female, Job: Craft
  TOMRF6 Race: Two or more Races, Sex: Female, Job: Craft
  WHM7 Race: White, Sex: Male, Job: Operatives
  BLKM7 Race: Black or African American, Sex: Male, Job: Operatives
  HISPM7 Race: Hispanic, Sex: Male, Job: Operatives
  ASIANM7 Race: Asian, Sex: Male, Job: Operatives
  AIANM7 Race: American Indian or Alaska Native, Sex: Male, Job: Operatives
  NHOPIM7 Race: Native Hawaiian or Other Pacific Islander, Sex: Male, Job: Operatives
  TOMRM7 Race: Two or more Races, Sex: Male, Job: Operatives
  WHF7 Race: White, Sex: Female, Job: Operatives
  BLKF7 Race: Black or African American, Sex: Female, Job: Operatives
  HISPF7 Race: Hispanic, Sex: Female, Job: Operatives
  ASIANF7 Race: Asian, Sex: Female, Job: Operatives
  AIANF7 Race: American Indian or Alaska Native, Sex: Female, Job: Operatives
  NHOPIF7 Race: Native Hawaiian or Other Pacific Islander, Sex: Female, Job: Operatives
  TOMRF7 Race: Two or more Races, Sex: Female, Job: Operatives
  WHM8 Race: White, Sex: Male, Job: Labors
  BLKM8 Race: Black or African American, Sex: Male, Job: Labors
  HISPM8 Race: Hispanic, Sex: Male, Job: Labors
  ASIANM8 Race: Asian, Sex: Male, Job: Labors
  AIANM8 Race: American Indian or Alaska Native, Sex: Male, Job: Labors
  NHOPIM8 Race: Native Hawaiian or Other Pacific Islander, Sex: Male, Job: Labors
  TOMRM8 Race: Two or more Races, Sex: Male, Job: Labors
  WHF8 Race: White, Sex: Female, Job: Labors
  BLKF8 Race: Black or African American, Sex: Female, Job: Labors
  NORC | The Equal Employment Opportunity Commission (EEOC)
  12
  HISPF8 Race: Hispanic, Sex: Female, Job: Labors
  ASIANF8 Race: Asian, Sex: Female, Job: Labors
  AIANF8 Race: American Indian or Alaska Native, Sex: Female, Job: Labors
  NHOPIF8 Race: Native Hawaiian or Other Pacific Islander, Sex: Female, Job: Labors
  TOMRF8 Race: Two or more Races, Sex: Female, Job: Labors
  WHM9 Race: White, Sex: Male, Job: Service
  BLKM9 Race: Black or African American, Sex: Male, Job: Service
  HISPM9 Race: Hispanic, Sex: Male, Job: Service
  ASIANM9 Race: Asian, Sex: Male, Job: Service
  AIANM9 Race: American Indian or Alaska Native, Sex: Male, Job: Service
  NHOPIM9 Race: Native Hawaiian or Other Pacific Islander, Sex: Male, Job: Service
  TOMRM9 Race: Two or more Races, Sex: Male, Job: Service
  WHF9 Race: White, Sex: Female, Job: Service
  BLKF9 Race: Black or African American, Sex: Female, Job: Service
  HISPF9 Race: Hispanic, Sex: Female, Job: Service
  ASIANF9 Race: Asian, Sex: Female, Job: Service
  AIANF9 Race: American Indian or Alaska Native, Sex: Female, Job: Service
  NHOPIF9 Race: Native Hawaiian or Other Pacific Islander, Sex: Female, Job: Service
  TOMRF9 Race: Two or more Races, Sex: Female, Job: Service
  WHM1_2 Race: White, Sex: Male, Job: Mid Off and Managers
  BLKM1_2 Race: Black or African American, Sex: Male, Job: Mid Off and Managers
  HISPM1_2 Race: Hispanic, Sex: Male, Job: Mid Off and Managers
  ASIANM1_2 Race: Asian, Sex: Male, Job: Mid Off and Managers
  AIANM1_2 Race: American Indian or Alaska Native, Sex: Male, Job: Mid Off and Managers
  NHOPIM1_2 Race: Native Hawaiian or Other Pacific Islander, Sex: Male, Job: Mid Off and Managers
  TOMRM1_2 Race: Two or more Races, Sex: Male, Job: Mid Off and Managers
  WHF1_2 Race: White, Sex: Female, Job: Mid Off and Managers
  BLKF1_2 Race: Black or African American, Sex: Female, Job: Mid Off and Managers
  HISPF1_2 Race: Hispanic, Sex: Female, Job: Mid Off and Managers
  ASIANF1_2 Race: Asian, Sex: Female, Job: Mid Off and Managers
  AIANF1_2 Race: American Indian or Alaska Native, Sex: Female, Job: Mid Off and Managers
  NHOPIF1_2 Race: Native Hawaiian or Other Pacific Islander, Sex: Female, Job: Mid Off and Managers
  TOMRF1_2 Race: Two or more Races, Sex: Female, Job: Mid Off and Managers

[caltang]

You posted this before - https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Discussions/If-you-re-good-at-Alteryx-coul...

I gave you the workflow that combines them all together. In addition, I've also mentioned several points here: https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Discussions/How-do-you-handle-the-nulls-An...

What are you trying to do exactly? How granular must your dimensions be?

[rively90]

Hi @caltang 

This is the one that I'm expecting to get this table: (please see the picture)

I can't open and run the file that you shared.

Could you tell me how I do this? Thank you! I'm hoping to get the expected results like the table below

[caltang]

The file I gave you is an Alteryx Packaged file. You have to import the package. You may get a warning saying that the version is different, but you can still proceed.

Now the Directory Tool I used will have an error warning because we cannot package Directory tool contents. 

That is why I gave you all the YXDB files. You need to create a folder to save all your YXDB files, then configure the Directory Tool to that folder path tou saved all the YXDBs in. Then it’ll work. 

This is far more efficient and effective than your multiple input files and using Union to combine them together. Plus processing will be faster. 

Please try that first before going on to your main request.

[rively90]

@caltang  Could you please guide me on how to insert 8 different CSV file in the same folder? Do we need to manually drag the input and Union them? I'm a bit lost

My output file is only 2 kb ??

[Peachyco]

@rively90 If the files have different columns, then yeah, you might have to bring them in via separate Input Data Tools so you can do the necessary fixes before you can Union them. There are quite a number of ways to do this, really.

If all of the files have the same columns/template, then you can put them all in one folder, and use just one Input Data Tool to bring all of them into your Alteryx workflow.

• You can use a wildcard (*) to read multiple files. Just make sure that only the necessary files are in that folder so Alteryx doesn't read irrelevant files.
• All of the records from all of the files will come together in one table. If you need to identify which file a record comes from, you can configure the option "Output File Name as Field" accordingly. This will add a column with the source file name/path.

 

[rively90]

@Peachyco  @caltang 

I did union them, but I got an error in the 2nd step:

Error: The field “[FullPath]” is missing. Compare the tool configuration with the input stream

It asked me about the control parameter. I did not understand what they are?

[caltang]

@rively90 kindly follow the steps below...

STEP 1

Download all your YXDBs from 2014 to 2021 into a folder which you can remember. Save it somewere simple like your Desktop. You should have something like this:

So you see, I decided to save it in a simple folder in my desktop - which includes the original .xlsx files along with my newly created .yxdb files.

Step 2

In the attached workflow, I used a Directory Tool to call the data in because the YXDBs all have different schemas. Because of this, I am unable to use the wildcard to call them in successfully. This is why Step 1 is so important, because I need to know where I saved the files.

So, you can seeI changed the Directory path to where I am storing my data in my folder as decided in Step 1. I also made note to the Directory tool to only call YXDB files. 

Step 3

The error you see comes from the Batch Macro I sent you. You need to connect it to a Directory Tool to call the Full Path as below:

Please make sure to select the Macro, then choose FullPath after the Directory tool.

The steps thereafter should combine all your files into 1 - nice and easy.

Please follow the steps above first, once you finish - report back here. Then we will decide what to do with your data thereafter + how you want to proceed to clean the data.

Hope this helps.

Best,

Calvin

[rively90]

@caltang how do I get yxdb from 2014 to 2021? I don't have it. I only have csv???

[caltang]

I gave you the full set last Thursday… https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Discussions/If-you-re-good-at-Alteryx-coul...

Please read through the comments, I’ve attached them there for your convenience.