BPC Transformation File Formula to add zero

[kyle_tetschlag]

Hello Experts

I am putting together a transformation file to pull in master data from a flat file. The dimension I am creating in BPC is 5 characters. I the flat file, the string is either 4 characters or 18 characters. For the 18 character stings, I am using *COL(1,1:5) but for the 4 character strings, I am not sure how to add a zero at the end to make 5 characters. Or how to tell if it is 4 or 18 characters in the transformation file. Is it possible to use the formula TEXT(COL(1),"00000") or IF statement that identifies is the string is 4 characters?

[former_member186338 · Accepted solution]

Instead of transformation file with very limited options use conversion file with JavaScript line:

EXTERNAL INTERNAL
*        js:%external%.toString().length==4 ? %external%.toString() + "0" : %external%.toString().slice(0, 5)

easy

[kyle_tetschlag · Accepted solution]

Hi Vadim - This works for one dimension where I am only pulling in COL(1). Thank you for that.

However I have another dimension that I am pulling in multiple columns and this specific column fits in the middle. Where again COL(1) is either 4 or 18 characters but want to pull in 5 characters. COL 6, 2, 3, 4, and 5 are fixed length if that helps.

ID=*COL(6)+*COL(2)+*COL(3)+*COL(4)+*COL(5)+*COL(1)+*COL(7)

[former_member186338 · Accepted solution]

kyle.tetschlag

Sorry, but your original question is correctly answered, please accept the correct answer!

Second - your new question is not described correctly:

COL 6, 2, 3, 4, and 5 are fixed length

What about COL(7)???

[kyle_tetschlag · Accepted solution]

I am creating another dimension by concatenating multiple columns. COL(1) is either 4 or 10 characters but I need to pull in 5 characters. COL 6, 2, 3, 4, 5 and 7 are fixed character length.

ID=*COL(6)+*COL(2)+*COL(3)+*COL(4)+*COL(5)+*COL(1)+*COL(7)

For example :

COL(6) = ??

COL(2) = ??

COL(3) = ???

COL(4) = ??????

COL(5) = ????

COL(1) = either ???? or ??????????. If ???? then ????0 otherwise pull first 5 characters of ??????????

COL(7) = ???

[former_member186338 · Accepted solution]

kyle.tetschlag

Move variable length column to the end of the string!

ID=*COL(6)+*COL(2)+*COL(3)+*COL(4)+*COL(5)+*COL(7)+*COL(1)

For example :

COL(6) = XX

COL(2) = XX

COL(3) = XXX

COL(4) = XXXXXX

COL(5) = XXXX

COL(7) = YYY

COL(1) = either ZZZZ or ZZZZZTTTTT. If ZZZZ then ZZZZ0 otherwise pull first 5 characters of ZZZZZTTTTT

XXXXXXXXXXXXXXXXXYYYZZZZ

or

XXXXXXXXXXXXXXXXXYYYZZZZZTTTTT

Then in Javascript you will always know the beginning of col1.

You can easily combine a new string:

js:%external%.toString().substr(0, 17 )+(%external%.toString().length==24 ? %external%.toString().substr(20, 4)+"0" : %external%.toString().substr(20, 5))+%external%.toString().substr(17, 3)