Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Don't forget to submit your entry for the Excellence Awards by October 30! | Need more information about the program? Check out the blog here

Limit data input by date [Oracle DB]

Highlighted
8 - Asteroid

I am connecting to an oracle DB and selecting a table. Firstly have to do a EDA on data but the tables are massive. I stopped the run at 9 million rows and 4Gb of data and it was still running.

I only want to bring in a year of data but the date/time columns are all treated as Strings. How would I change the string format "20150401" to a date stamp and filtering only the last 365 days before bringing into Alteryx?

 

I'm currently using the data input tool but I am kinda assuming I'd have to use the In-Database tool? Don't know how I'd go about that nor the SQL syntax needed. Any help would be appreciated.

 

 

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

Hello @wmcclure,

 

If you want to select the data from a specific time having the values as string, since you have the values ordered like that (yyyymmdd) you could change them with a to_number function and select the ones in a specific period.

 

Another option you could have would be to use the like function and select the ones that start with 2015 (as per your example).

 

Could this work out for you?

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Regards

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

@wmcclure wrote:

How would I change the string format "20150401" to a date stamp and filtering only the last 365 days before bringing into Alteryx?


Is "20150401" the format of date values in your table, or is this the value you (or the user) will input to set the filter date?

 


@wmcclure wrote:

I'm currently using the data input tool but I am kinda assuming I'd have to use the In-Database tool? Don't know how I'd go about that nor the SQL syntax needed. Any help would be appreciated.


You have several options including Input Data, Dynamic Input, and the In-Database tools. While any could work, the best one to use will depend on a few things like what/how you'd like to update the query, the size of the table, and various IT considerations.  

Highlighted
8 - Asteroid

@CharlieS 

 

The "20150401" is how it is in the database. I tried the other suggestion to use a "like" statement to pull only this year and it still was over 90 million records! I really need to trim this down to a more manageable size, a 52 minute alteryx run just to pull data is unfeasible. I'm using the in-DB tools now to see if I can filter from there first then use other modules after I at least do the heavy lifting in-DB.

 

I have a time stamp column that I can bring in, previous criteria made this one not possible at first, it is structured yyyy/mm/dd HH:MM:SS

 

How would I created a rolling date filter/query to grab only the last 4 days of data?

Labels