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)
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.
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?
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.
