DSXchange: DataStage and IBM Websphere Data Integration Forum
View next topic
View previous topic
Add To Favorites
This topic has been marked "Resolved."
Author Message
jackson.eyton
Participant



Joined: 26 Oct 2017
Posts: 10

Points: 275

Post Posted: Mon Nov 13, 2017 10:07 am Reply with quote    Back to top    

DataStage® Release: 11x
Job Type: Parallel
OS: Windows
Hi guys,
I am working on replicating some trending jobs from our old data warehouse system. Those jobs had to be run manually and the last day of the month manually entered as the trending date. The issue is that sometimes the trend was run before or after the actual last day of month depending on where the last day of the month actually aligns with processing. I was thinking that there must be a way to collect the date for the last day of the month, and IF the current date is only 1-4 days into the month then get and use the previous months last day date. Every time I go to write out this derivation I keep confusing myself. Any help anyone could provide would be very greatly appreciated!!

_________________
-Me
qt_ky



Group memberships:
Premium Members

Joined: 03 Aug 2011
Posts: 2654
Location: USA
Points: 19691

Post Posted: Mon Nov 13, 2017 1:48 pm Reply with quote    Back to top    

Just to clarify, are you asking about using parallel date functions within a parallel job or is this being done within a sequence job?

One way to approach it is to take the day of the month of the current system date, then test if that is <= 4 (or some cut-off day number), if so then reconstruct the date in the same month and year and use 1 as the day of the month number, then take that result and subtract 1 day from it to get the last day of the previous month.

_________________
Choose a job you love, and you will never have to work a day in your life. - Confucius
Rate this response:  
jackson.eyton
Participant



Joined: 26 Oct 2017
Posts: 10

Points: 275

Post Posted: Wed Nov 15, 2017 11:41 am Reply with quote    Back to top    

Hi again qt_ky!
This is indeed for a parallel job, I found the functions necessary are not available in sequence jobs. I was able to take your direction there and get something working rather well!
I had some other things I needed to do as well and was able to apply similar logic. What I have done so far is exactly what you said, take the current date and get the date of the first day of the following month, and subtract 1 day. That gives me the end of the current month. IF the day of the month of the current date is <= 4 then I just get the first day of the current month and subtract 1 day to get the last day of the previous month.

I am having an issue comparing variable values in triggers on a sequence job now but I will make a new post for that here soon if I can't figure it out.

Thanks again!!!

Variables Used:
CurrentYear = YearFromDate(CurrentDate())

CurrentMonth = MonthFromDate(CurrentDate())

CurrentDay = MonthDayFromDate(CurrentDate())

LastDayOfCurrentMonth = DateFromDaysSince(-1, DateFromComponents(YearFromDate(CurrentDate()), MonthFromDate(CurrentDate())+1, 1))

LastDayOfPrevMonth = DateFromDaysSince(-1, DateFromComponents(YearFromDate(CurrentDate()), MonthFromDate(CurrentDate()), 1))

ClosestEndOfMonthDate = IF CurrentDay <= 4 THEN LastDayOfPrevMonth ELSE LastDayOfCurrentMonth

_________________
-Me
Rate this response:  
Not yet rated
ray.wurlod

Premium Poster
Participant

Group memberships:
Premium Members, Inner Circle, Australia Usergroup, Server to Parallel Transition Group

Joined: 23 Oct 2002
Posts: 54038
Location: Sydney, Australia
Points: 293144

Post Posted: Wed Nov 15, 2017 6:14 pm Reply with quote    Back to top    

MONTH.LAST and MONTH.FIRST are available as Transforms in server jobs.

_________________
RXP Services Ltd
Melbourne | Canberra | Sydney | Hong Kong | Hobart | Brisbane
currently hiring: Canberra, Sydney and Melbourne
Rate this response:  
Not yet rated
qt_ky



Group memberships:
Premium Members

Joined: 03 Aug 2011
Posts: 2654
Location: USA
Points: 19691

Post Posted: Thu Nov 16, 2017 9:02 am Reply with quote    Back to top    

Glad we could help! Thanks for sharing the variables you implemented.

_________________
Choose a job you love, and you will never have to work a day in your life. - Confucius
Rate this response:  
Not yet rated
jackson.eyton
Participant



Joined: 26 Oct 2017
Posts: 10

Points: 275

Post Posted: Thu Nov 16, 2017 11:12 am Reply with quote    Back to top    

ray.wurlod wrote:
MONTH.LAST and MONTH.FIRST are available as Transforms in server jobs. ...


That is good to know, and the only purpose of this particular job is to collect various date details and run some conditional logic on them so I might recreate the job as a server job. Thanks!

_________________
-Me
Rate this response:  
Not yet rated
Display posts from previous:       

Add To Favorites
View next topic
View previous topic
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum



Powered by phpBB © 2001, 2002 phpBB Group
Theme & Graphics by Daz :: Portal by Smartor
All times are GMT - 6 Hours