Project Default formatting

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

AmeyJoshi14
Participant
Posts: 334
Joined: Fri Dec 01, 2006 5:17 am
Location: Texas

Project Default formatting

Post by AmeyJoshi14 »

Hi,

We are facing problem in our project , we are using ISValid('Timestamp',InputColumn) to check whether the InputColumn is a valid timestamp or not.
The format of the input string is '20Nov2009 14:10:10',but in project default value is %dd-%mm-%yyyy %hh:%nn:%ss. I have unchecked the project default box and gave the desried value that is '%dd%mmm%yyyy %hh:%nn:%ss'
but it is not working...still it is considering the format as mentioned in the default project.
Also i have gone through one of the post and tried all teh options mentioned , but no luck :(
viewtopic.php?t=95920&highlight=project+default

My question is, can we override the project default value and use the format in which data is coming ...? :roll:
Thanks all for the help!!
http://findingjobsindatastage.blogspot.com/
Theory is when you know all and nothing works. Practice is when all works and nobody knows why. In this case we have put together theory and practice: nothing works. and nobody knows why! (Albert Einstein)
balajisr
Charter Member
Charter Member
Posts: 785
Joined: Thu Jul 28, 2005 8:58 am

Post by balajisr »

Convert to timestamp using function StringToTimestamp and then check for IsValid.
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

Weak advice - the conversion function will fail if the string is not a valid timestamp. The IsValid() function is intended to be applied to a string.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
balajisr
Charter Member
Charter Member
Posts: 785
Joined: Thu Jul 28, 2005 8:58 am

Post by balajisr »

Ray,

My project default timestamp format is "%yyyy-%mm-%dd %hh:%nn:%ss"

I have two input rows which has to be in format "%dd%mmm%yyyy %hh:%nn;%ss"

1.20Nov2009 14:10:10
2.Nov202009 14:10:10

In the transfomer i have the following code:

Code: Select all

IsValid('Timestamp',StringToTimestamp(DSLink2.Col1,'%dd%mmm%yyyy %hh:%nn;%ss'))
IsValid() Output:

1.20Nov2009 14:10:10 = 1
2.Nov202009 14:10:10 = 0

Anything wrong with this approach? Please advise.
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

Yes. The StringToTimestamp() function will fail before the IsValid() function is even executed, if the source column is not a valid timestamp based on the given format string. You need to test with IsValid() first. And in this case you probably need two tests, one for each format.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
AmeyJoshi14
Participant
Posts: 334
Joined: Fri Dec 01, 2006 5:17 am
Location: Texas

Post by AmeyJoshi14 »

balajisr wrote:Ray,

My project default timestamp format is "%yyyy-%mm-%dd %hh:%nn:%ss"

I have two input rows which has to be in format "%dd%mmm%yyyy %hh:%nn;%ss"

1.20Nov2009 14:10:10
2.Nov202009 14:10:10

In the transfomer i have the following code:

Code: Select all

IsValid('Timestamp',StringToTimestamp(DSLink2.Col1,'%dd%mmm%yyyy %hh:%nn;%ss'))
IsValid() Output:

1.20Nov2009 14:10:10 = 1
2.Nov202009 14:10:10 = 0

Anything wrong with this approach? Please advise.
Sorry for the late reply.. :oops:
I actually use the above function, the job is giving warning..as Ray has pointed.. for every records ,whose date fomat doesn' t matches with the desired date format.
While searching in this forum , i found that inorder to achieve this, we have to use stage variables... :shock:
Is there any patch or any other method to achieve this..ideally if we uncheck the "Project Default" box and give the desired format ..it should work ...right?? :?
Thanks all for the reply... :!:
http://findingjobsindatastage.blogspot.com/
Theory is when you know all and nothing works. Practice is when all works and nobody knows why. In this case we have put together theory and practice: nothing works. and nobody knows why! (Albert Einstein)
balajisr
Charter Member
Charter Member
Posts: 785
Joined: Thu Jul 28, 2005 8:58 am

Post by balajisr »

I actually use the above function, the job is giving warning..as Ray has pointed.. for every records ,whose date fomat doesn' t matches with the desired date format.
Can you please post those records which gives warning?
AmeyJoshi14
Participant
Posts: 334
Joined: Fri Dec 01, 2006 5:17 am
Location: Texas

Post by AmeyJoshi14 »

balajisr wrote:
I actually use the above function, the job is giving warning..as Ray has pointed.. for every records ,whose date fomat doesn' t matches with the desired date format.
Can you please post those records which gives warning?
Hi,

Thanks for the reply!!

Code: Select all

StringToTimestamp(Input.Column,'%dd%mmm%yyyy %hh:%nn:%ss')
For the below records its working fine:
'20Nov2009 14:10:10'
'21Nov2009 14:10:10'
'22Nov2009 14:10:10'
'23Nov2009 14:10:10'

But for the other records,it is giving me warning: :oops: :?
20112009 14:11:10
22112009 14:11:10
http://findingjobsindatastage.blogspot.com/
Theory is when you know all and nothing works. Practice is when all works and nobody knows why. In this case we have put together theory and practice: nothing works. and nobody knows why! (Albert Einstein)
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

"11" does not match "%mmm" (which expects three characters). You must get your dates in to a consistent format.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
AmeyJoshi14
Participant
Posts: 334
Joined: Fri Dec 01, 2006 5:17 am
Location: Texas

Post by AmeyJoshi14 »

ray.wurlod wrote:"11" does not match "%mmm" (which expects three characters). You must get your dates in to a consistent format. ...
Thanks for the reply!! :)

Actually I want to check whether the Timestamp from the input string IsValid.... So i have used the IsValid function, but the problem is , format , datastage consider only those date format as valid date which are mentioned in project default, even though I have uncheck the project default and gave the desired format. :?
Is there is any patch for the same.... by going through this forum i have found that I have to use stage variables to check the valid timestamp... :shock:
Its really strange.... :shock: even though i have uncheck the project default...still its considering the project default format as valid..

Again thanks for the all the help... :)
http://findingjobsindatastage.blogspot.com/
Theory is when you know all and nothing works. Practice is when all works and nobody knows why. In this case we have put together theory and practice: nothing works. and nobody knows why! (Albert Einstein)
balajisr
Charter Member
Charter Member
Posts: 785
Joined: Thu Jul 28, 2005 8:58 am

Post by balajisr »

Why don't you try using Ray's IsValid() routine. You need to develop server jobs to use this.
AmeyJoshi14
Participant
Posts: 334
Joined: Fri Dec 01, 2006 5:17 am
Location: Texas

Post by AmeyJoshi14 »

balajisr wrote:Why don't you try using Ray's IsValid() routine. You need to develop server jobs to use this.
Thanks of the reply!

Actually the problem is that we can't use the server job....(we have to develop only parallel jobs... :roll: )..... I would like to use .....Ray's IsValid() routine.... :D
http://findingjobsindatastage.blogspot.com/
Theory is when you know all and nothing works. Practice is when all works and nobody knows why. In this case we have put together theory and practice: nothing works. and nobody knows why! (Albert Einstein)
balajisr
Charter Member
Charter Member
Posts: 785
Joined: Thu Jul 28, 2005 8:58 am

Post by balajisr »

Actually the problem is that we can't use the server job....(we have to develop only parallel jobs... Rolling Eyes )....
That's ridiculous. :shock:

FYI:

Code: Select all

IsValid('Timestamp',StringToTimestamp(DSLink2.Col1,'%dd%mmm%yyyy %hh:%nn;%ss')) 
The above code with below data did not produce any warnings when i ran the job.

20112009 14:11:10
22112009 14:11:10

Is it probably due to version 7.5? :roll:
AmeyJoshi14
Participant
Posts: 334
Joined: Fri Dec 01, 2006 5:17 am
Location: Texas

Post by AmeyJoshi14 »

balajisr wrote:
Actually the problem is that we can't use the server job....(we have to develop only parallel jobs... Rolling Eyes )....
That's ridiculous. :shock:

FYI:

Code: Select all

IsValid('Timestamp',StringToTimestamp(DSLink2.Col1,'%dd%mmm%yyyy %hh:%nn;%ss')) 
The above code with below data did not produce any warnings when i ran the job.

20112009 14:11:10
22112009 14:11:10

Is it probably due to version 7.5? :roll:
Yes, I think for the 8X version , this problem exists... :x
I am really unable to unsderstand why the project default checkbox is not working... :? Is it because there is some problem while installation...or is there any patch for the same... :shock:

Thanks for the reply !! :wink:
http://findingjobsindatastage.blogspot.com/
Theory is when you know all and nothing works. Practice is when all works and nobody knows why. In this case we have put together theory and practice: nothing works. and nobody knows why! (Albert Einstein)
balajisr
Charter Member
Charter Member
Posts: 785
Joined: Thu Jul 28, 2005 8:58 am

Post by balajisr »

Transformer date format is ISO 8601 standard (YYYY-MM-DD) regardless of project or job default date format.
Post Reply