nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Matching the Field Names Properly

StockMarket

Hello

I have to create a BATCH MACRO in which I have to process multiple csv files which have got the following 10 fields inside them -

FileName,Ticker,OLD_Date,Time,Open,High,Low,Close,Volume,Open Interest

FileName
Ticker
OLD_Date
Time
Open
High
Low
Close
Volume
Open Interest

Now, ever once in a while, I end up with a csv file, which is either missing a particular required field, or have got some additional field which should not be there and sometimes there are cases of having spelling mistakes in the field names as well.

So I am trying to design a workflow which will WARN or ALERT me if there is any such error present within my csv files. I want to make sure that all the new data files that I process through my workflow, have the DITTO SAME data structure.

Here are the steps that I am trying to accomplish in this workflow -

First of all, convert all the Fields to Lower Case

Then edit the "open interest" field by removing the space and adding an underscore.

Then make sure that the csv file has got these 10 Fields inside it

AND in the same sequence,

AND with the same spelling for each field -

filename
ticker
old_date
time
open
high
low
close
volume
open_interest "note that I have removed the space and added an underscore instead"

Please suggest me which particular Alteryx tools will help me to do the above steps in most efficient manner.

I have also attached the Alteryx Worflow Package .yxzp file and the sample data file, so that you can easily open it up on your machine. I have used the latest version of Alteryx for creating this workflow. You may please have a look.

Thanks and Regards

Sample Data.png

Sample Workflow.png

Workfflow Files Attached.png

Batch Macro

Accepted answers

AkimasaKajitani

Hi @StockMarket

Let's back to my uploaded workflow.

Point is follows.

- Field Info tool shows the schema of data stream.

- If reference field list, you can compare between the list and output of field info tool.

- You can use Message tool to output the error when you want.

- The error conditions is that you can decide from comparing result.

- You can control that if the error occurs, the workflow go or no go on Workflow option.

And I have one correction. Count tool is not needed. Please delete it.

danilang

Hi @StockMarket

As per your request, here's an explanation of how @AkimasaKajitani's field comparison works

The Field Info tool returns the list of fields along with their sizes and types. The following ReordID tool just add a FieldOrder tool that increments for each row. Combined they give you an output that looks like this

This is the same list that is reproduced in the Text Input called Master Field List. These two lists are compared in the Join tool with the records matched on order, name, type and size. All the records that match come out the J(oin) anchor of the Join tool. The unmatched records from the L input come out L output. Ditto for the R inputs and outputs.

In a perfect run, all the fields on both sides will match. If a field is missing, misspelled, or out of order, that's where the L and R outputs come into play. Each of them is connected to a Count tool which simply counts the number of records. The next tool is a Message tool, which writes a message in the results window if a condition is met. In this case the message gets triggered if the count <> 0. you'll notice that I use a Count tool, where @AkimasaKajitani used a Summarize in count mode. The difference between the 2 is that the Count tool always returns a number even if that number is 0. In the number of records going to a Summarize tool is 0, then the Summarize tool doesn't output any thing at all.

To test this, change the name of one of the fields in your input file. When you run it you'll see messages like this in your results window. For this test I changed "FileName" to "File Name"

Looking at the Brose tools connected to the L and R outputs of the Join tool will tell you what fields are at fault.

Dan

example5v3(Differing Fields).yxmc

All comments

StockMarket

Any ideas, anyone ? I thought that my question would be a fairly normal user case scenario, where someone would like to do the checking of the Data Fields, before going ahead with the various type of data cleaning and processing steps etc. So I was expecting quick response on this.

Maybe I have made some mistake in uploading the files, because of which you guys are not being able to download and run the batch macro on your system.

I have uploaded the Batch Macro on the community forum, for the very first time, so I am not totally sure, how to correctly upload it, so that other users can run it on their machines.

So I am requesting from the members who have helped me in the past, that can you please tell if you are able to run the batch macro uploaded by me ?

Thanks a lot and Best Regards.

@Qiu @AkimasaKajitani @afv2688

AkimasaKajitani

Hi @StockMarket

You can use Message tool and you have to put the reference fields list into the workflow.

And I make sample workflow. This is prototype because there are a lot of things to consider to be perfect workflow.

Message tool configuration sample:

If you want to stop the workflow running when error occurs, you can use the "cancel Running Workflow on Error" option.

Error Message Sample:

example5.yxzp

AkimasaKajitani

Hi @StockMarket

Let's back to my uploaded workflow.

Point is follows.

- Field Info tool shows the schema of data stream.

- If reference field list, you can compare between the list and output of field info tool.

- You can use Message tool to output the error when you want.

- The error conditions is that you can decide from comparing result.

- You can control that if the error occurs, the workflow go or no go on Workflow option.

And I have one correction. Count tool is not needed. Please delete it.

danilang

Hi @StockMarket

As per your request, here's an explanation of how @AkimasaKajitani's field comparison works

To test this, change the name of one of the fields in your input file. When you run it you'll see messages like this in your results window. For this test I changed "FileName" to "File Name"

Looking at the Brose tools connected to the L and R outputs of the Join tool will tell you what fields are at fault.

Dan

example5v3(Differing Fields).yxmc

Quick Links

Popular Tags

This months top contributors

atcodedog05 19598

Qiu 15911

binu_acs 15783

MarqueeCrew 13710

apathetichell 13703