current date () - 6 months

[karthi_gana]

All,

I have a file like below.

100|201005|2.345
100|201006|1.342
100|201007|0.345
100|201008|5.343
100|201009|1.375
100|201010|6.345
100|201011|7.345
100|201012|8.345
100|201101|1.345
100|201102|1.222
100|201103|6.333
200|201005|2.345
200|201006|1.342
200|201007|0.345
200|201008|5.343
200|201009|1.375
200|201010|6.345
200|201011|7.345
200|201012|8.345
200|201101|1.345
200|201102|1.222
200|201103|6.333

I would like filter only the last 6 month data in another file.

100|201010|6.345
100|201011|7.345
100|201012|8.345
100|201101|1.345
100|201102|1.222
100|201103|6.333
200|201010|6.345
200|201011|7.345
200|201012|8.345
200|201101|1.345
200|201102|1.222
200|201103|6.333

Inputs are welcome!

[chulett]

What have you tried? What are your thoughts on this matter?

[karthi_gana]

I did this in DB as below.

dateadd(mm,-6,getdate())

I don't know the equivalent function available in datastage.

[chulett]

Sorry, it just irks me when people post a question and it seems like there's been no thought put into getting the answer themselves, where they just post a problem and then sit back and wait for the answer to fall into their laps. Not saying that is what is going on here, just wanted to get that off my chest.

One solution: viewtopic.php?t=111266

[chulett]

Another option - take the advice in the previous post and write your own version in C++. There's nothing out of the box to do this.

[MarkB]

Well, here is one way to get your results, though I'd check your resulting file carefully :D .

For this, define your YYYYMM column in your sequential file as integer. Create a stage variable - in this example I named it 'CUTOFF'. This is defined as follows:

Code: Select all

StringToDecimal(DateToString(DateFromDaysSince(-182,CurrentDate()),"%yyyy%mm"),"trunc_zero"). 

In your transformer stage, have a constraint on the link to your output file, and do a comparison, like DSLink3.YEARMO >= CUTOFF.

It gives the correct output, though I'm probably jumping through more hoops than necessary to do this ....

[mobashshar]

In transformer stage, Sort the input Date Column in DESC order and using constraint send out the First Six Rows.

If you are running your job in parallel use this in constraint to filter the last 6 months: (((@INROWNUM -1) * @NUMPARTITIONS) + @PARTITIONNUM + 1) <= 6

Hope this helps

[MarkB]

In transformer stage, Sort the input Date Column in DESC order and using constraint send out the First Six Rows.

If you are running your job in parallel use this in constraint to filter the last 6 months: (((@INROWNUM -1) * @NUMPARTITIONS) + @PARTITIONNUM + 1) <= 6

Hope this helps

Huh???

I think you misunderstood him - he wants the data from the last 6 months - not just 6 rows of data ...

[Tejas Pujari]

use following code in a stage variable and use stage variable in constraints.

If (MonthFromDate(DSLink2.date) > (MonthFromDate(CurrentDate())-7)) And (MonthFromDate(DSLink2.date) <= (MonthFromDate(CurrentDate()))) Then 1 Else 0

[ray.wurlod]

How about DateFromDaysSince() with an offset of -183 ?

[priyadarshikunal]

How about DateFromDaysSince() with an offset of -183 ? ...

Depends on the definition on 6 months. it can be either that or

Code: Select all

if(link.field[5,2] <=6) Then (link.field[1,4]-1):12+link.field[5,2] - 6 else link.field[1,4]:link.field[5,2]-6

[chulett]

You can't simply drop the month value by six without regard for the day.

[priyadarshikunal]

You can't simply drop the month value by six without regard for the day.

I think you can if the date is in yyyymm format as shown in the example in first post.

[chulett]

Sorry, you're correct, that makes the technique acceptable.

[ray.wurlod]

Depends on the definition on 6 months.

No it doesn't. I understand the requirement to be "the date 6 months prior to current date".