 # How to treat data import with text representing negative number

Hi,

I have a file which contains my FACT table data (financial data) where the data has alpha characters to show negative numbers.

Example Alphas convert to numbers
0 1 2 3 4 5 6 7 8 9
p q r s t u v w x y

So
Field that come in as 14445v = -1444.56 (negative number)
Field that come in as 14445 = 144.45 (positive number)
Etc. etc.

I am wondering how to treat the alpha characters so I can transform the numbers to the negatives - I can replace values to bring in the numbers into the column but not sure about the step to show the negative numbers. Also is there a way to insert the decimal place the only way I thought was to split the column and then merge with the decimal inserted?

Also wondering whether I should further unpivot the data - Columns are as follows (Period is theYear and Month Number so 202001 is July 2020) Cheers

Can you add a simple example of the scenario so that some testing and ideas can be worked on?

Thanks
Sam So the Lines Above should finish up as the following I am assuming I would be best off using measures to calculate the YTD amounts so those YTD colums could be deleted.

Cheers

Hi,

As Sam quite rightly points out it would be better to have an example.

It is always fun to see how data may be presented to us, this is a first to me.

I would solve this in the Query Editor, create a 3 column query with the conversion, p,0, -1, , q,1,-1, including 0 to 9 with +1 etc.
then split the fact table column by most right character, and merge the queries to get the digit from the conversion query, join the columns, and multiply with the -1 or + 1 column.
Inserting the decimal place, divide by 100.

Paul Thanks Paul,

Sorry If the example posted above wasn’t enough. I’m not exactly sure what you mean by creating a 3 column query? Is that still using the replace value function?

Cheers

Hi Paul,

Ok I worked it through as follows:

1. Replace Values - I put the number and a negative sign. 2. Then converted the column to a whole number which moved the dash/negative symbol to the front and converted into a negative number.
3. Then did a custom Column divide by 100 to bring in the decimal place Seems to have worked a treat! Thanks for you help I dont think I would have got to this without a prompt or two!

Cheers

Hi,

Glad it worked out for you, I was under the impression though, that your issue involved a range of letters from p to y. You solved it with replace values, which is fine, there are many ways to solve things fortunately. My solution, given the assumption of a range of letters, was to create a table in Excel, import it and merge as described, which is taking more effort but may be a better solution if you have many letters.

Paul Yes there was a range but only a small one as per my first post

Thanks again for the help
Cheers