If you are unable to create a new account, please email support@bspsoftware.com

 

Lookup variable in Job reading text file - need to convert to date

Started by DWrangler, 08 Apr 2011 01:44:07 PM

Previous topic - Next topic

DWrangler

I need to find the minimum date from an input file that is a .txt file.  My current attempt it to use a variable in the job that submits a lookup against my text file.  The problem is that the date is in a mm-dd-yyyy format which means it doesn't select the correct date.  Since it's going against a text file, the ToDate function isn't recognized. 

Does anyone have any ideas/tricks to help me over this hurdle?  I don't have much control over the data sent to me, so I need to do something from the DataManager/ETL side.

Thanks!

spudly

Create a calculated field that converts the string date into yyyymmdd format as a string.  If you do a min on the calculated string value it will produce the minimum date you are looking for.

This only works if you know that the string dates in the text file are valid dates.