Error data summary for Incorrect data

Hi all, I am working in a file and I want to get a solution for my output. I have file in which I have applied macro according to the schema, there are multiple files with different sheets, the macros help to consolidate the file according to matched schema. Now, I have a new request from my team 1) I want to know if all the files have the data or not, if the data is coming from all the files or not, I want to get the summary for how many files have the data and how many don't have the data. 2) In my input file I have few rows which have different data rather than the usual data, Ex- Date column has few data in which some random texts or numbers are there, So I need to push only the data which has correct value, if the column has incorrect data type then the file will push only the correct data and if there is incorrect data I will get a new summary of the incorrect data file.

Can this be done ? I can provide examples if anyone can help me out. Thanks

Developer

Input

Join

Preparation

Accepted answers

All comments

Raj

@Kaish
yes this can be done

first thing first , update your macro to pull the complete file path as well
so when you get the consolidated data you can have all the file paths
then you can summarize this an using join tool you can figure out the empty sheets and empty files

2 -you will be creating multiple filters to filter out the exceptional data form the regular data

in short this can be achieved

hope this helps you
mark done if solved.

Kaish

Thank you for your reply @Raj , could you explain, I am new to Alteryx and I am unable to perform the tasks properly.

Raj

@Kaish
ok so first we will update the batch macro you are been using as of now.
just change from configuration of input tool
output file name as filed - "NO" to "Full path"

once this solved we will move on next step to comparison

caltang

If you’re following this: https://knowledge.alteryx.com/index/s/article/The-Ultimate-Input-Data-Flowchart-1583459854309

It will be useful to use the directory tool as an input to see how many files are there. Then you can compare with what @Raj suggested at the end to see if the count from the start ties to the end. You can also join based on the file path, which is a one to many relationship. From there you can see which file path was left behind -> that indicates no data if you’ve done your macro correctly that is.

Raj

@Kaish
were you able to follow the first step?

Kaish

@Raj While doing "Full Path" from "NO". I am not getting the desired output, when it was "NO", I got proper results. What should I do now ? There are many files which has multiple sheets, so the data output has some issues while doing Full path. Any Solution ?

jdminton

@Kaish There were a couple of changes needed for your macros to work. The first was to change your input data tool (not provided) to "<List of Sheet Names>" in the Table or Query field in the configuration. Also, the first macro needs to pass the filename with the sheet name included in the file path. If you want to feed multiple Excel files through, you will want to add a directory tool to select the files and a dynamic input to default to the list of sheet names.

Copy of Excel Tab macro.yxmc

Copy of New Workflow Excel.yxmc

Example Macros.yxzp

Kaish

Hi @jdminton Actually I have multiple files, so I am working using directory tool, I have used these macros in directory tool to read these files. My requirement is: I have many files and in these files there are multiple tabs. out of all the tabs in those files, there is one such tab which is similar for all, that one single tab has same schema attributes and my data is extracted with the schema and positional value and in the end I need the consolidation of the data which is appended. When I used my macros it ran properly and my consolidated data was appended accordingly. Can you guide me how can I work on directory tool with the dynamic input to achieve my solution as I mentioned in my blog. Thanks

jdminton

Ok, it was a bit confusing since the macros provided were not working as you sent them. There are ways to test the data, but you've not provided any data except years and numbers. You could validate the years exist in each table, but I don't know how you would validate the numbers.

Is there a reason the data is coming from random locations in Excel files? Is this data available from another source? I don't think the validation will help if you are trying to validate using the same files.

Kaish

Hi @jdminton kindly find the example of my data in this blog, I have attached the files https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Discussions/Consolidation-of-different-tabs-from-different-excel-files/m-p/1271750#M317349 . You can add one more file "Order 4" with only the header with blank data. Because as my requirement is to find the number of files from which the data is not coming, so if you add one more file without any data will work as my example. Kindly guide me if I can resolve this. I get the files from a particular tool, extract the data and then store it in a single folder, there are 15-20 files in that folder. Kindly guide me through the steps.

jdminton

@Kaish your request is not making sense to me in relation to the previous requests. The files you are now referencing are different than the ones you referenced in this thread. Maybe this is a separate question, but it isn't clear.

Based solely on this question, you can use the directory tool to bring in the files from the folder, use the macros (see in attached workflow) to bring in the data from Excel, and compare back to the directory list.

Compare Files.yxzp

Raj

@Kaish
find the workflow attached
mark done if solved.

kiash Help.yxzp

kiash Help.yxmd

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703