topic Is there anyway to make a column of negative numbers positive? in Discussions

Is there anyway to make a column of negative numbers positive?

OhThatArabGuy — Fri, 01 Nov 2024 23:22:20 GMT

I'm having an issue where I want my data to be shown as absolute values. This is a query (in snowflake) with over 200 variable columns (depending on the filters) so it makes it difficult to include every column in the query.

I'm ideally looking for a way to find all the columns with negative values and making them positive. (As I write this out, I guess I could transpose, place all the values in one column. Make an absolute column and transpose back?)

Any help would be greatly appreciated!

Re: Is there anyway to make a column of negative numbers positive?

Dan_Obermiller — Sat, 02 Nov 2024 01:07:52 GMT

It sounds like you are not sure how many data columns you will have. If the table will look like your example (but with more columns), I think using the Tables > Stack command makes the most sense.

Put all of your data columns into the Stack Columns field. Click OK. Right-click on the Data column header and choose New Formula Column > Transform > Absolute Value. With this new table now choose Tables > Split. Specify the Abs{Data] as the Split Columns. Put the Label column in the Split By field. Make sure the Drop All radio button is selected and click OK.

You should have a table that looks like the original, but with no negative values.

Re: Is there anyway to make a column of negative numbers positive?

txnelson — Sat, 02 Nov 2024 05:20:55 GMT

@Dan_Obermiller method works well. But if all of your columns are numeric, the following will convert all of the negative values in the data table to positive.

names default to here(1);
dt=current data table();

dt[0,0]=abs(dt[0,0]);