TM1 Forum

Posted: **Tue Jul 21, 2020 11:17 am**

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

Posted: **Tue Jul 21, 2020 11:22 am**

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.

Posted: **Tue Jul 21, 2020 12:20 pm**

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;

Posted: **Tue Jul 21, 2020 5:43 pm**

Markuss wrote: ↑Tue Jul 21, 2020 12:20 pm 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.

TM1 Forum

TM1 Data insert into SQL

TM1 Data insert into SQL

Re: TM1 Data insert into SQL

Re: TM1 Data insert into SQL

Re: TM1 Data insert into SQL