How to calculate length of the string in transformation file

[gajendra_moond]

Hello all

I have tried a number of ways and I am not able to calculate the length of the incoming field in transformation file for my data load. Here is the issue.

I have an incoming string of length 10 and I need to use it to update multiple dimensions which will need first 2, 4 ,6 , 8 characters respectively.

Now in transformation file ID=ID(1:2) works perfect. However, the BW InfoObject will have multiple values as shown below:

ID	Description
AB	Business
ABCD	Business Organization
ABCDEF	Business Line
ABCDEFGH	Product Family
ABCDEFGHIJ	Product Line
ABCDEFGHIJKL	Brand

I want to update my dimension for Business Organization. I need to update only the records where the length of the incoming string is 4. If I do ID(1:4), I will get the duplicate records and will see the ID less than 4 characters in rejected records. The latter is not such a big issue but the former one is as I need to fetch the right description as well. The formula should check the length and then only pass the record else a dummy value.

A formula to achieve this in the transformation file is what I am looking for.

Regards

Gajendra

[gajendra_moond · Accepted solution]

Hello all

Any help or other work around using BPC transformation and conversion will be appreciated.

[former_member186338 · Accepted solution]

Start_routine badi?

Vadim

[gajendra_moond · Accepted solution]

Hi Vadim

Thanks for your response!

I do not wish to go back to BW, ABAP or BADI for this simple requirement. I know it can be done there but for me that is the last resort due to project constraints.

I am looking to achieve this by using transformation and conversion file only, preferably transformation file.

Regards
Gajendra

[former_member186338 · Accepted solution]

You may try to use Java Script in the conversion file and convert all members with the legth<>4 to something dummy... or empty...

js:%external%.length==4 ? %external% : "something"

Not able to check now, just an idea.

B.R. Vadim

[gajendra_moond · Accepted solution]

Thanks Vadim for the quick response!

I am also trying something similar and really appreciate your input here. However, I am not sure if the conversion is really happening.

Here is what I have done:

But the strings <> 4 in length are still passing through.

[former_member186338 · Accepted solution]

You have to put JS:... in the INTERNAL column, not in the formula column.

Vadim

[gajendra_moond · Accepted solution]

I am sorry...must have been mind freeze....

I did that after posting and still it is not modifying the string not equal to length 4 to "something".

[former_member186338 · Accepted solution]

Try this:

js:%external%.toString().length==4 ? %external%.toString() : "something"

[former_member186338 · Accepted solution]

and have you properly referenced conversion file in the transformation file?

[gajendra_moond · Accepted solution]

My mistake there.

I put the dimension name instead of "ID". It worked this time.

Thanks a ton!

[gajendra_moond · Accepted solution]

On a side note,

Is it possible to skip the records altogether that do not have length = 4?

[former_member186338 · Accepted solution]

Not this way

Try to play with "?" in INTERNAL:

??     *skip

?????? *skip

...

"?" - any single character

Vadim

[gajendra_moond · Accepted solution]

Hi Vadim

It worked

Thanks so much again!

Since I know what all lengths I need to ignore, it was easy for me. The "INTERNAL" will have js:*skip.

Thanks again!!

[former_member186338 · Accepted solution]

May be INTERNAL - *skip without js:?

[gajendra_moond · Accepted solution]

I tried initially with *skip only but it did not work. When changed to js:*skip, it worked for me.

[former_member186338 · Accepted solution]

Interesting thing... no idea why this syntax is working and not working without js:

But anyway the issue is solved

B.R. Vadim