DSXchange

Hi guys, I need your help.

I know the function YearweekFromDate, this function returns the week number in the year from the given date, but now I need to know some function or logic that return to me the week number in the month.

Following the example:

September 2012
Sunday | Monday | Tuesday | Wednesday | Thursday | Friday | Saturday | Week number in the month
X | X | X | X | X | X | 1 | 1
2 | 3 | 4 | 5 | 6 | 7 | 8 | 2
9 | 10 | 11 | 12 | 13 | 14 | 15 | 3
16 | 17 | 18 | 19 | 20 | 21 | 22 | 4
23 | 24 | 25 | 26 | 27 | 28 | 29 | 5
30 | X | X | X | X | X | X | 6

Other than div(day,7)+1?

Or you need to consider whether the first day of the month is a Thursday/Friday etc? Without thinking too much in to that, subtracting the day of week for 1st of the month from current day then doing a div... if less than 1 then 1st week of month else add 1, something like that

A little bit of simple math would do the work: Subtract the YearWeek of the first day of the month from the YearWeek of your incoming date and add 1.

Regards,

Hey , can you please clarify are you implementing ds job or are you implementing this logic in the any existing job.

As per my knowledge we can use range lookup in the lkp stage.
If not kindly ignore my answer.

Example:- If you are using lkp you can add range lkp on based on your month and week use min of the month.

Regards,
Sree

jwiles wrote:A little bit of simple math would do the work: Subtract the YearWeek of the first day of the month from the YearWeek of your incoming date and add 1.

Regards,

Thanks for the Reply. To get the day of week for 1st of the month for the given date. I am implementing with below steps. I want to know any easy solution for this.

1)Extract the month and year for the given date by using field function

2)For date I will concatenate with 01

ex-15-Sep-05
extract sep and 05 and concatenate with 01 so result will be 01-Sep-05

That is the easy solution as it allows the YearweekFromDate() function to perform most of the work for you (determining the week values to subtract from each other) and will meet the business rules requirements as shown by your examples. Most any other logic written to meet those requirements would be more elaborate and difficult to explain and maintain.

There is no single function within DataStage to determine the number of weeks from an arbitrary starting date, so a little bit of extra work is required. There is a similar thread to this one that has been active recently, but the requirements were a little different and hence the suggested solution is also different.

Regards,

Thanks jwilis for ur reply

I understood the logic you explained but i need following clarification.

To get the first day of the month for incoming date before calculating YearWeek .I am doing following steps and i want to know whether i am correct or not


1)Extract the month and year for the incoming date by using field function

2)For date I will concatenate with 01

ex-15-Sep-05
extract sep and 05 and concatenate with 01 so result will be 01-Sep-05



Is there any easy to achieve the above logic(getting the first day of the month for incoming date)

Have you tried it? Don't wait for us to give our "blessing" Trial and error is a great learning tool so take advantage of it.

As you're apparently working with string data coming in, that is an appropriate method and is as easy as any other. Ensure that you use the proper format specifiers when you convert the strings to date datatypes (well documented in the Parallel Job Developer's Guide and discussed many times within the forum).

Regards,

Mod(day+WeekdayFromDate(01MMYYYY)-1),7)+1 sounds pretty easy to me.

You can set first day of week to be Sunday or Monday down to your requirement

Kryton, I don't believe that Mod() based on the day is the correct function for his requirements. No matter the origin day used for WeekdayFromDate(), the result of your logic would be different for each day within a physical week, instead of the same as required by the OP's rules.

For example, based on his calendar of Sep 2012 and using the default origin day:

Both Sep 19 and Sep 20 are in week 4 of Sep 2012.

Sep 19, 2012: Mod(19+WdFD(01092012)-1,7)+1 = Mod(19+7-1,7)+1 = Mod(25,7)+1 = 5
Sep 20, 2012: Mod(20+WdFD(01092012)-1,7)+1 = Mod(20+7-1,7)+1 = Mod(26,7)+1 = 6

Division by 7 instead of Mod(x,7) would be more appropriate but would require that an adjustment based on the beginning date of the first full week within the particular month (for Sep 2012, the first full week starts on Sep 2) in order to meet the example's rules:

AsInteger((day+WdFD(01MMYYYY)+5)/7)

Sep 19, 2012: AsInteger((19+WdFD(01092012)+5)/7) = AsInteger((19+7+5)/7) = AsInteger(31/7) = 4
Sep 20, 2012: AsInteger(20+7+5) = AsInteger(32/7) = 4

I think the difference in the YearweekFromDate() values is easier to code and understand in this case (because no one has to figure out why in the heck I have a 5 in the above logic ):

YearweekFromDate(DDMMYYYY) - YearweekFromDate(01MMYYYY) + 1

YwFD(19092012) - YwFD(01092012) + 1 = 38-35+1 = 4
YwFD(20092012) - YwFD(01092012) + 1 = 38-35+1 = 4

vamsi/igor: Of course, use the correct format for the date strings or use date datatypes as the arguments (DDMMYYYY is not the default DS date format, YYYY-MM-DD is, but your system may be different)

Regards,

I meant DIV... I scribbled it down without thinking too heavily in to it... however not following your logic

Sep 19, 2012: AsInteger((19+WdFD(01092012)+5)/7) = AsInteger((19+7+5)/7) = AsInteger(31/7) = 4
Sep 20, 2012: AsInteger(20+7+5) = AsInteger(32/7) = 4

Why would you add five? Weekday from Date of 01092012 would give 6 (or 7?) since it starts on a Saturday (and assuming a week starts on a Sunday)... so without actually testing

you would have 19+6 (or 19+7-1 depending on what weekdayfromdate actually gives), div by 7 gives 3 + 1... week 4.

All that is being done is that the day is shifted on X amount to account for the fact the week doesn't start on Sunday/Monday

Yup: DIV makes the difference!

I just came at it from the goal of the (whole-number) result of the division being the answer rather than adding one to it afterwards...either way produces the same results. The 5 comes from this:

Whatever the date of the first Saturday of the week, it needs to shifted to 13 in order for the division alone to provide the correct result (based on the shown example). 13/7 = 1.86, 14/7 = 2. So if the 1st falls on Saturday: 1 + 7 + 5 = 13. If the 1st falls on Monday, Saturday is the 6th: 6 + 2 + 5 = 13.

The division-based math, either way, gets geek points but I believe using the YearweekFromDate()-based logic is cleaner and easier for most developers to read, understand, and maintain later on without having to decipher the other math (although they would become that much better for doing so ). I haven't run any sort of test to find out if one or the other method is more efficient execution-wise.

Regards,

The division-based math, either way, gets geek points

That's all I'm after :D

I couldn't say which is quicker either and not likely to try test. The question would be, how does DataStage calculate YearWeekFromDate if not with a bit of maths?

@jwilis.Thanks for the input.I am getting required output by using following derivation as you mentioned.


Input is varchar



Stage variables

Field(DSLink3.id,'-',2)-Svmon
Field(DSLink3.id,'-',3)-Svyea
'01'-Svdat

(YearweekFromDate(StringToDate(DSLink3.id,"%dd-%mmm-%yyyy"))-YearweekFromDate(StringToDate(Svdat:'-':Svmon:'-' :Svyea,"%dd-%mmm-%yyyy")))+1

Kryt0n, I expect it's probably calling underlying system library functions, probably from libstdc or similar.

vamsi: Great! Don't forget to mark the thread as resolved.

Regards,