Mapping issue..

[Former Member]

Hi,

I have a requirement where source field TRANS-PRICE consists of 15 digits value from flat file.

Below are the values coming from flat files: Source fields TRANS-PRICE have 6 decimals.

Input:

000000258060000

000000333060000

000000345061111

000000112362345

.............................

.............................

.............................

Outuput:

258.060000

333.060000

345.061111

112.362345

....................

.....................

......................

Target field should look like as per the above output with a decimal point and leading zeroes removed + left justified .

I'm trying to acheive by using;

TRANS-PRICE --- FormatNumber --- UDF (how we use 6 decimail points?? ) -

---

Target field

Please let me know the UDF code for the same..

Sameer!

[Former Member · Accepted solution]

Hi,

Map Like below.

Source---------------------divide---formatnumber(#.000000 decimal separator .)---target
             constant(1000000)-------

Thanks and Regards,

Prakasu.M

[Former Member · Accepted solution]

Thanks Prakasu it's done without UDF as you suggested.

Thanks All.

Sameer

[Former Member · Accepted solution]

Hi Sameer,

Please use the simple mapping explained by Prakasu without involving UDF. This wil surely work for all the inputs.

Regards, Gaurav.

[Former Member · Accepted solution]

SOLVED!

[Former Member]

Hi Sameer,

Try with the below UDF:

import java.util.regex.Matcher;

import java.util.regex.Pattern;

String input = "00000000000000023202203203827";

String regex = "^0*";

Pattern p = Pattern.compile(regex);

Matcher m = p.matcher(input);

String output = m.replaceAll("");

return output;

Thanks,

[nabendu_sen]

Hi Sameer,

You dont have to go for Java UDF, you can easily achieve this by message mapping. Just do like below:

1. User <replaceString> function under Text. 1st Input the <Field Value>, 2nd Input Constant <000000>, 3rd Input empty <>

2. Then use <divide> function under Arithmetic. 1st Input <Output of replaceString>, 2nd Input <1000000>.

3. Then use <formatNumber> function under Arithmetic. Input <Output of divide> with NumberFormat <0.000000> and Decimal Separator <.>.

4. Map the output of <formatNumber> to target field. See the Display Queue. You have achieved it.

Thanks,

Nabendu.

[Former Member]

Hi Sameer,

Nabendu's reply looks fine to me except for the below point.

The mapping will fail to provide the correct result for an input such as "000000112000000". For such an input you would expect the target field to be populated as 112.000000 however, with this mapping your result will be 0.000112

Here, an important point to be noted is that the mapping designed above will return the desired result only if Six consecutive Zeros (000000) appear in the input source only at the beginning. However, if these apear anywhere else in the input source then this mapping is bound to return an incorrect result.

So, going for an UDF to replace leading 0's is definitely better.

And for getting the precision upto 6 decimal places, you can use formatNumber function from Arithmetic group of functions with the following input:

1. Number Format: 000.000000 (Here, the format signifies that the out put will always be of xxx.yyyyyy format where xxx is the three characters before decimal and yyyyyy is the 6 characters after decimal)

2. Decimal Separator: "."

I hope this helps.

Regards, Gaurav

[Former Member]

Hi Kumar,

Thank you for your prompt response.

As per the Nabendu's reply logic get fails in mapping as I tried the same.

The mapping will fail to provide the correct result for an input such as "000000112000000". For such an input you would expect the target field to be populated as 112.000000 however, with this mapping your result will be 0.000112

Would appreciate if you help me in UDF logic since I'm not Java background.

After getting the UDF logic 'll map the same to below FormatNumber as you suggested.

1. Number Format: 000.000000 (Here, the format signifies that the out put will always be of xxx.yyyyyy format where xxx is the three characters before decimal and yyyyyy is the 6 characters after decimal)

2. Decimal Separator: "."

Could you pls confirm the below UDF works?

public static String removeLeadingZeros(String str) {

if (str == null)

return null;

char[] chars = str.toCharArray();

int index = 0;

for (; index < str.length(); index++)

{

if (charsindex != '0')

break;

}

return (index == 0) ? str : str.substring(index);

}

Regards,

Sameer!

[Former Member]

In the Arithemetic function FormatNumber, if you use the format "00000.000000" it will add the 6 decimals.

Remove Leading zero:

public static String removeLeadingZeros(String str) {

if (str == null)

return null;

char[] chars = str.toCharArray();

int index = 0;

for (; index < str.length(); index++)

{

if (chars[index] != '0')

break;

}

return (index == 0) ? str : str.substring(index);

}

Edited by: Martin Lavoie Rousseau on Nov 17, 2010 10:03 PM