TM1 Data insert into SQL

[Markuss]

Hi All,

Another interesting problem came up when working with TM1 as newbie
Well, two issues actually..

1st - I'm writing out element description (using vDesc=ATTRS('WIN',vWIN,'Description'); ) to SQL table

result is "123456 - Product Description"

How to remove part of string starting with 1st character and ending with "-" character. so it would be only "Product Description"

2nd - Some of product descriptions contains single quotation mark - '1234567 - SANTA'S BASKET' which leads SQL to fail with message "Incorrect syntax near 'S'

How do I wrap this correctly?

Thanks in advance

[Wim Gielis]

Hi,

Problem 1: you can add an additional alias (or text attribute) to the relevant dimension. If you handle these operations when you update the dimensions, you don't have to bother anywhere else (input/reporting/send to DWH/loading data/calculations/...)

Have a look at the DELET function together with SCAN.

Problem 2: Again, SCAN, and this time INSRT function instead of DELET. However, if it's possible that multiple single quotation marks occur in a string then a WHILE... END loop can be needed. It's not difficult at all, you can find many examples on the forum here.

[Markuss]

Thank you Wim,

Found something that solves #2 issue - removes single quotation mark. I think that code below can be adapted to resolve #1 issue same time..

Code: Select all

vDesc= ATTRS('WIN',vWIN,'Description');

#Remove singe quote from description string. without this loop SQL insert will fail.
iClean = 0;
WHILE ( iClean <> 1 );
	iPos = SCAN ( '''', vDesc );
	IF ( iPos <> 0 );
		vDesc  = DELET (vDesc , iPos, 1 );
	ELSE;
		iClean = 1;
	ENDIF;
END;

[scrumthing]

Found something that solves #2 issue - removes single quotation mark. I think that code below can be adapted to resolve #1 issue same time..

Yes, you could do that easily with the snippet. But I would stay with Wims suggestion to add a second alias or if you only need it for your export a simple text attribut would do as well. If you don't want to play around with the dimension process you could fix that probably with a rule on the elementattributes cube. But I don't know all your edge cases with the data.