Python ECC SAP Extract into Alteryx Workflow

[NBPSE]

I am using the Python Tool to import data from ECC SAP. The Amounts are showing up as 100,000- (a string) for a negative number. I've tried using a data cleanse tool, a formula tool to do ToNumber(Amount), and using a Select Tool to change the string to a number. Instead of fixing the issue it is deleting my data.

How can I fix the data to read a string of 100,000- to a number of -100,000?

[apathetichell · Accepted solution]

Let's assume you can are using the DVW tools (or another third party tool) to read your data out of ECC. First off - you can usually map this in the tool to figure out how to process it. Next -- let's try these steps in a formula tool:

1) --- replace([field],',','') --- we are replacing a comma --- because we'll convert it to a number. --- we'll do this operation in the same field --- so let's call it field.

2) next---- we'll create a new numeric field --- let's call it field 2.

we'll use an if statement like this:

if contains([field],'-') then -1*tonumber(replace(field,'-','')) else tonumber(field) endif

this says--- if it has a - sign --- let's replace the - and multiple the number by -1... else we'll keep it as the number.

[NBPSE]

Thank you, this worked!