How to compare a csv file and excel file to identify duplicates?

[Sunithaprdp]

I am new to Alteryx. I wanted compare a csv file with 136 characters with an excel worksheet to identify lines with common identifier. The output should be similar to the csv input file. But it should contain only the details of common identifiers. Please help. I am attaching samples.

As per the sample : there is a common identifier between csv SAMPLE and excel sample that is '00012-0123-45 with name ABCDE. So the output should be the eliminated csv file with no second line that is:

QTY0001205789622021STUVW00000001.23400000000000123.0000000000009999.99000000020000000002379.670000000000000.000000000002345.78.

The text file should only contain first line of SAMPLEs.

[Sunithaprdp]

oh okay, thank you for the insights. Will try it now and update you the same.

[Sunithaprdp]

It is successful! Thank you so much ! I am going to test with some more additional data.

Will try to learn why each tool is used and get back to you for help.

Thank you so much for helping me with this. 

[Sunithaprdp]

I have a question, in case if we have multiple files for different states. If we want to keep each file to a shared INPUT folder and we need alteryx to pick those files one by one and then provide an output respectively. What can be done in such a case?

[binuacs]

@Sunithaprdpquestions

1. What about the lookup excel file? should that also will be separate for each input file?

2. Are all the .csv files are having the same schema (same columns)?

3. Should the output file also created separate for each input file?

[Anum19]

Thanks for the solution, I was also looking for the solution for the same problem, and it help me a lot...,

[Sunithaprdp]

Actually we do have excel sheets for different programs say :
ProgramA, ProgramB, ProgramC.

Similarly text files would be for those programs.

However as I have mentioned on samples there is no such field wherein which the program names are mentioned.

But we should get output as such for each programs with specific names like for eg:

1Q2021 Program A, 1Q2021 Program B...

My question is : Is it possible to save all text files into a common Text File Input Folder and all excel sheet to a common Excel Input Folder and to receive an output specific to each program with respective naming convention?

[binuacs]

@Sunithaprdp yes you can use the directory tool to get the files names from the folder. To get only the .csv files you need to configure the directory like below. Same for the .xlsx files

[Sunithaprdp]

Sorry I didn't understand. So in the existing workflow we have to put directory before Output Icon?

How to get it specific for each programs as the program name is not mentioned on the input text file as well as the input excel worksheet?

So is it possible to navigate the output to a folder named OUTPUT? If then what should be the file name that we should give there as we need multiple output files for each programs with a single workflow?

[binuacs]

@Sunithaprdp  to answer your questions 

1. So in the existing workflow we have to put directory before Output Icon? No 

the directory tool is to read all the input files. so you need to use them instead of the input tool, this required some additional changes to the existing workflow as well. is there any mention of the programs in the names of the input files? or how you are identiying the given files is from which program? 

2.So is it possible to navigate the output to a folder named OUTPUT?  yes you can, 

3.if you can identify these files based on the program name, then I think we can use those names for the output files as well

[Sunithaprdp]

Yes there is a standard naming convention:

For eg:

In case of excel it would be : 1Q21 Program A Worksheet.xlsx

In case of text file it would be : 1Q21 Program A _ 12345 and 1Q21 Program A_45678 

for Program A : excel worksheet would be the same though there would be different text files.