topic Re: Convert string to big decimal in Talend Studio

Convert string to big decimal

NNayal1600240775 — Sat, 16 Nov 2024 01:27:25 GMT

Hi ,

I have data like 00016546M,00000203Q.

Where the last digit alphabet represents:

M: -1

N: -2

O: -3

P : -4

Q: -5

and so on.

My o/p should be convert the last digit to the equivalent number but the minus "-" sign should be at the beginning. and then divide by 100.

For eg if i/p is 00000200P THEN o/p should be -20.07.

How can i achieve this in tmap?

Re: Convert string to big decimal

Anonymous — Wed, 16 Sep 2020 09:36:28 GMT

Use org.apache.commons.lang3.StringUtils.stripStart("00016546M", "0") will strip the start character "0", and use tReplaceList will replace the letter with equivalent number.

Regard

Shong

Re: Convert string to big decimal

NNayal1600240775 — Wed, 16 Sep 2020 09:49:32 GMT

Hi Shong through query I am able to achieve the result.

Below is the query for 1 sample:

SELECT CASE WHEN '000015O' LIKE '%O' THEN CONCAT('-',SUBSTRING('000015O',1,(regexp_instr('000015O','O'))-1) ,'3')/100 END;

Now I need to convert this query in tmap. Could you please help

Re: Convert string to big decimal

Anonymous — Thu, 17 Sep 2020 06:44:14 GMT

On tMap, use expression:

"-"+org.apache.commons.lang3.StringUtils.stripStart(row1.value, "0")

Before tMap, you need to use tReplaceList to replace the letter with equivalent number.

Re: Convert string to big decimal

vikramk — Thu, 17 Sep 2020 06:48:55 GMT

Hi NNayal1600240775,

Please follow below approach, make necessary changes as per your requirement

in tmap:

converted string to decimal

in next tmap

Output:

Re: Convert string to big decimal

NNayal1600240775 — Thu, 17 Sep 2020 07:42:27 GMT

Vikram the approach you discussed looks good to me.

But this approach has 3 steps in between , to avoide that I loaded everything as string in a temporary table in snowflake then wrote sql query :

CASE

WHEN COLUMN_NAME LIKE '%J' THEN CAST(CONCAT('-',SUBSTRING(COLUMN_NAME,1,(regexp_instr(COLUMN_NAME,'J'))-1),'1') AS INTEGER)/100

WHEN COLUMN_NAME LIKE '%K' THEN CAST(CONCAT('-',SUBSTRING(COLUMN_NAME,1,(regexp_instr(COLUMN_NAME,'K'))-1),'2') AS INTEGER)/100

Thanks for sending me this approach in tmap. I will try this out.

Thanks for the help. Really appreciate it.