Date format - Log error - PX

ds_search2008 · Post by **ds_search2008** » Fri Jun 05, 2009 6:38 am

Dear Expert,

I'm facing an issue while checking the date format in transformer stage.
The date value from the input file is defined as Char[10] and the corresponding target datatype is Date.
I'm checking the date format if "yyyy-mm-dd" otherwise I'm rejecting the rows. I have used the following constraints in my transformer

correct path
------------------

Code: Select all

constraint IsValid("Date",EFF_DT)=1  AND IsValid("Date",EXP_DT)=1

Reject path
-----------------

Code: Select all

constraint IsValid("Date",EFF_DT)=0  AND IsValid("Date",EXP_DT)=0  OR (EFF_DT=' ' EXP_DT=' ')

For the input values

Rows EFF_DT EXP_DT
1 2000-01-01 2000-10-10 - (correct)
2 ' ' ' ' - (reject)
3 2/02/2001 2/11/2001 - (reject)

I'm able to get the records in the respective path. The job design is green. However log shows couple of fatal errors as follows:

1. APT_CombinedOperatorController,0: Data string ' ' does not match format '%yyyy-%mm-%dd': an integer was expected to match tag %yyyy.
Data string ' ' does not match format '%yyyy-%mm-%dd': an integer was expected to match tag %yyyy.
Data string ' ' does not match format '%yyyy-%mm-%dd': an integer was expected to match tag %yyyy.
2. APT_CombinedOperatorController,0: Data string '2/11/2001 ' does not match format '%yyyy-%mm-%dd': the value for tag %yyyy has fewer characters than expected.

Could you please share your ideas on how I can suppress these fatal errors?

Thanks & Regards

chulett · Post by **chulett** » Fri Jun 05, 2009 6:46 am

You should check validity before transforming the string to a date. And your reject constraint is wrong - what's in the "OR" is invalid and you seem to need more parens to get the AND/OR priority correct.

ds_search2008 · Post by **ds_search2008** » Fri Jun 05, 2009 7:42 am

Hello Craig,

Thanks a lot for your valuable time and help. I tried different probabilities.

Code: Select all

using stage variables:       Derivation
------------------------        -------------
EffDtValidate - Integer -2    If IsValid("Date",EFF_DT) = 1 Then 1 Else 0
ExpDtValidate-Interger-2    If IsValid("Date",EXP_DT) = 1 Then 1 Else 0

Transformer valid path constraint

Code: Select all

EffDtValidate=1  AND ExpDtValidate=1

Reject path constraint

Code: Select all

EffDtValidate=0  OR ExpDtValidate=0

Input values
--------------

Code: Select all

Rows      EFF_DT        EXP_DT 
1            2000-01-01  2000-10-10 - (Valid) 
2            ' '                ' '               - (reject) 
3            2/02/2001    2/11/2001 - (reject)

Still my job log shows same fatal error message. Kindly help me in this regard.

Many Thanks & Kind Regards

ajay.vaidyanathan · Post by **ajay.vaidyanathan** » Fri Jun 05, 2009 8:26 am

Try out this particular check. In your Transformer define a "FLAG" in your Stage Variable and perform this check in your Stage Variable "If IsValid('date', EFF_DT) Then "Y" Else "N". Based on this Flag value, you can proceed further to either process the record or reject it. Perform this similar kind of check for EXP_DT also.

chulett · Post by **chulett** » Fri Jun 05, 2009 9:10 am

Setting a 'flag' to Y/N is no different from setting it to 1/0 and it's all extra work. Just use the expression in the derivation and the Boolean output will automatically be set. For example:

svIsValidEffDt -> IsValid("Date",EFF_DT)

Then a simple "If svIsValidEffDt Then X else Y" can be used in a derivation or just "svIsvalidEffDt" or "Not(svIsvalidEffDt)" in a constraint.

ds_search2008 · Post by **ds_search2008** » Thu Jun 11, 2009 2:41 am

Ajay, thanks a lot for your time.

Craig, thanks a ton for your time and guidance. First of all I apologize for my late reply. I have used the below derivation and my job works fine now.

Code: Select all

If EFF_DT = '' Or Not(IsValid('Date', StringToDate(EFF_DT , "%yyyy-%mm-%dd"))) Then 0 Else 1

Thanks again for your kind help and valuable time.