Page 1 of 1
TM1 TI: string date validation
Posted: Fri Aug 01, 2014 5:22 pm
by vovanenok
Hi
In my cube I have dates in format "yyyy-mm-dd". I need to implement data validation so my TI process catches invalid dates (empty and all those don't match the pattern "yyyy-mm-dd").
If I try to use DayNo() function, the process fails when encounters the first invalid date. So it seems I need to use multiple SUBSTs and validate separate patterns. Does anyone have anything on this matter?
Thanks in advance!
Re: TM1 TI: string date validation
Posted: Fri Aug 01, 2014 5:41 pm
by tomok
Why would you not want to store dates in TM1 as serial numbers? Everything is so much easier when you do so. To display the serial number as yyyy-mm-dd in TM1 you simply format it so show that. Behind the scenes the data is just a number. If the dates are serial numbers and formatted as you want, this will stop a lot of invalidate dates from ever getting in to the cube in the first place.
Re: TM1 TI: string date validation
Posted: Fri Aug 01, 2014 6:09 pm
by vovanenok
Thanks Tomok
I was thinking about this, but haven't learned that question enough. So this time I think I can make the decision towards numbers.
Just one question. I noticed that if I set "yyyy-mm-dd" format, it displays that accordingly, but when I double click the cell to edit it in a cube viewer, the format changes to the TM1 server regional settings (in my case: "mm/dd/yyyy"). Can I somehow prevent it?
Re: TM1 TI: string date validation
Posted: Fri Aug 01, 2014 6:11 pm
by vovanenok
Also I'm not able to copy/paste values
Re: TM1 TI: string date validation
Posted: Fri Aug 01, 2014 6:13 pm
by vovanenok
vovanenok wrote:Also I'm not able to copy/paste values
Just realized that the reason is in format difference: looks like it's getting confused with server's and my local regional settings
Does Architect use local regional settings?
On attached screenshot you can see how the format changes when I double click the cell
Re: TM1 TI: string date validation
Posted: Fri Aug 01, 2014 6:31 pm
by declanr
It's worth looking into other avenues as you are but in the event you go back to just wanting to validate the string input; the below should work (might require a few tweaks as I haven't tested it.)
Code: Select all
If ( Long ( sDate ) <> 10 );
ItemReject ( 'Invalid Date Character Encountered: "' | sDate |'"' );
Else;
iCount = 1;
iMax = 10;
While ( iCount <= iMax );
sString = Subst ( sDate, iCount, 1 );
If ( ( iCount = 5 ) % ( iCount = 8 ) );
If ( sString @<> '-' );
ItemReject ( 'Invalid Date Character Encountered: "' | sDate |'"' );
EndIf;
ElseIf ( ( Code ( sString, 1 ) < 48 ) % ( Code ( sString, 1 ) > 57 ));
ItemReject ( 'Invalid Date Character Encountered: "' | sDate |'"' );
EndIf;
iCount = iCount + 1;
End;
EndIf;
sYear = Subst ( sDate, 1, 4 );
sMonth = Subst ( sDate, 6, 2 );
sDay = Subst ( sDate, 9, 2 );
nYear = StringToNumber ( sYear );
nMonth = StringToNumber ( sMonth );
nDay = StringToNumber ( sDay );
If ( ( nMonth < 1 ) % ( nMonth > 12 ) );
ItemReject ( 'Invalide Month: "' | sMonth | '"' );
ElseIf ( nMonth = 2 );
nDiv4 = nYear \ 4;
nDiv100 = nYear \ 100;
nDiv400 = nYear \ 400;
If ( Round ( nDiv4 ) = nDiv4 );
If ( Round ( nDiv100 ) = nDiv100 );
If ( Round ( nDiv400 ) = nDiv400 );
sLeapYear = 'Yes';
Else;
sLeapYear = 'No';
EndIf;
Else;
sLeapYear = 'Yes';
EndIf;
Else;
sLeapYear = 'No';
EndIf;
If ( ( sLeapYear @= 'Yes' ) & ( nDay > 29 ) );
ItemReject ( 'Invalid Day: "' | sDay | '" of year: "' | sYear | '"' );
ElseIf ( ( sLeapYear @= 'No' ) & ( nDay > 28 ) );
ItemReject ( 'Invalid Day: "' | sDay | '" of year: "' | sYear | '"' );
EndIf;
ElseIf ( ( nMonth = 4 ) % ( nMonth = 6 ) % ( nMonth = 9 ) % ( nMonth = 11 ) );
If ( nDay > 30 );
ItemReject ( 'Invalid Day: "' | sDay | '" of year: "' | sYear | '"' );
EndIf;
ElseIf ( nDay > 31 );
ItemReject ( 'Invalid Day: "' | sDay | '" of year: "' | sYear | '"' );
EndIf;
Re: TM1 TI: string date validation
Posted: Fri Aug 01, 2014 6:36 pm
by vovanenok
Thanks declanr for useful code. it will be definitely handy!
Re: TM1 TI: string date validation
Posted: Thu Aug 07, 2014 4:29 pm
by vovanenok
I would like to bump this topic again as my last question didn't get any answer.
I'm trying to use number with custom format (yyyy-mm-dd), but when I double-click the cell to edit it in Architect, the format changes to the server local one (mm/dd/yyyy). However that format doesn't work when I edit the cell. It expects my local machine date format.
I'm experimenting with Jan 25, 2013 so I see where my year / month / day are. Please see my actions on the screen shot below.
Also it doesn't allow me to copy paste values . I'm able to copy/paste values only after changing element format to my local one.
Did anyone encounter such issue?
Re: TM1 TI: string date validation
Posted: Tue Aug 12, 2014 4:12 am
by Andy Key
No solution to your problem, as far as I am aware, Server Explorer has always behaved this way.
Re: TM1 TI: string date validation
Posted: Tue Aug 12, 2014 5:59 pm
by vovanenok
Andy thanks for the confirmation.