TM1 TI: string date validation

[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!

[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.

[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?

[vovanenok]

Also I'm not able to copy/paste values

[vovanenok]

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

[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;

[vovanenok]

Thanks declanr for useful code. it will be definitely handy!

[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?

[Andy Key]

No solution to your problem, as far as I am aware, Server Explorer has always behaved this way.

[vovanenok]

Andy thanks for the confirmation.