Multiple Steps inside a Workflow

[StockMarket]

Hello

I am new to Alteryx and I have to design a workflow, which involves many different steps in it. I am looking for some assistance in how these multiple steps can be done.

The csv data file that I am working with has Stock Market Data in the form of 1-minute time intervals in it. Although the actual data file is quite big, having millions of rows in it, but I have made a very small sample out of it for sharing on the forum. All this data has a constant schema and have the following 9 Fields inside it -

Ticker

Date

Time

Open

High

Low

Close

Volume

Open Interest

I have also uploaded the sample CSV File used in this work, as well as the Alteryx Worflow Package .yxzp, so that you can easily open it up on your machine. I have used the latest version of Alteryx for creating these workflows. You may please have a look.

After importing the csv file into the Alteryx, I apply the “AUTO FIELD” tool so that all fields are identified correctly according to the data inside them. Now I have to perform these additional steps on this data.

1. Remove Spaces “Leading and Trailing White Spaces both” from all the names given in the "TICKER" FIELD

2. Remove the “.NFO Suffix” from all the names given in the "TICKER" FIELD

3. Apply the Multi Step SORTING according to these 3 Fields, one by one, so that all the data is arranged in Proper Order –
Date - Ascending Order – “Oldest Date at the top and newest date at the bottom”
Ticker – Alphabetic Ascending Order
Time - Ascending Order

4. After doing the above steps, I need to filter the data into different categories based on the following conditions –

If either of these 3 suffix "-I, -II, or -III" are present at the very end in the names given inside the "TICKER" FIELD then separate that data as csv file and name it by adding a suffix of “_Futures” to whatever the name was of the original data input file. In this case, the name would become “20190131_Futures.csv” file.

And all the remaining data, which does not have those 3 suffix, should be separated as csv file and name it by adding a suffix of “_Options” to whatever the name was of the original data input file. In this case, the name would become “20190131_Options.csv” file.

5. Actually before creating the above-mentioned two output csv files, I want to perform a few additional steps as well, but I am not sure if that could be done inside the same data flow or do I need to create a separate flow for them, once the csv files are generated!

For the Output of the _Futures.csv data, I need to add a New Field by the name of “Month Number” and I need to put in the values inside them based on this logic – If the "TICKER" FIELD has the suffix of “-I” then put 1 inside Month Number Field, if it is “-II” then put 2 and if it is “-III” then put 3 and if it is none of the above then put 0 into Month Number Field.

Same way, I need to add a New Filed by the name of “Month Number” to the _Options.csv data and I need to put in the values inside them based on this logic – If the "TICKER" FIELD contains the value of “19JAN” then put 1 inside Month Number Field, if it contains “19FEB” then put 2 and if it contains “19MAR” then put 3 and if it is none of the above then put 0 into Month Number Field.

When you open up the attached Alteryx Worflow Package .yxzp file, you will notice that I have been abl

 

e to do most of the above tasks to some extent, but I am not able to define the Filter Condition as explained in point 4 above. I can write the filter condition for “-I” but how to include all three into the filter, like "-I, -II, or -III" ? And I have not been able to define the method of saving the output files according to the input file names. A few other steps could not be done yet, as I am not being able to figure out the ways to do them.

Any help that you can provide in this, will be greatly appreciated.

Thanks a lot

 

[AngelosPachis]

Hi @StockMarket 

Welcome to the Alteryx community. Thank you for taking the time to phrase your question properly and add all requirements, it really makes much much  easier to answer.

I have attached a workflow that addresses all the steps you mentioned. I have added some comments in the tools or in the annotation, to make it clear what's going on in each tool. Let me know if you have any questions.

Also note that for the stream coming out of the false output anchor of the filter tool (data for _Options.csv) you not only have Jan, Feb and Mar as months in your dataset but also Dec. I have added two calcs, one accounting for the existence of other months apart from Jan, Feb, Mar and one that will assign 0 as month number to all other months apart from those mentioned above. Keep the calc that works for you.

Hope that helps,

Regards,

Angelos

[DavidP · Accepted solution]

Hi @StockMarket 

Here is an updated version of your workflow with the rules as you described. I added some documentation to explain the steps I added.

[StockMarket]

Hello @AnglosPachis and @DavidP

Thank you so much to both of you, for helping me out on this. The solutions provided by both of you are working perfectly fine. I have implemented them inside my workflow.

The only issue that remains is that I am not being able to get the Output Tool to work properly. When I drag and drop the "Output Data Tool" into the flow, it is asking me for the File Name and Location for the Output Files. But I donot want to provide this information MANUALLY. Instead I want it to automatically save the output csv files into the same location as the "Input File Location" and also to automatically name the output files based on the input file name itself. Just as DavidP has done it in the below snapshot -

 

When I try to implement it into my workflow, I get this error -

 

Can someone please suggest how to AUTOMATICALLY get these 2 output files -  “20190131_Futures.csv” and “20190131_Options.csv” saved in the respective location of the input file, which is - "C:\Data" in this case.

I have also attached the UPDATED Alteryx Worflow Package .yxzp file, so that you may have a look.

Thank you so much.

PS: For converting the Date and Time Fields according to Alteryx, I am using the method suggested by DavidP. Although the method of Angelos also works well, but that involves additional steps of first creating a duplicate Time Field and then renaming it, etc. So I am trying to get the work done in the minimum steps possible.

[AngelosPachis]

Hi @StockMarket ,

You can configure the output tool as shown below 

That will save your output in the desired location, but the name will not be output1.csv as written in the textbox, but whatever is included in the FileName field of your data. You can also untick the Keep Field in Output tickbox, so that will remove the FileName field from your output dataset.

Let me know if that worked for you