Split one column into 2 columns using values

[dreweezy]

Hi, this is a follow up of my previous questions regarding bucket creation for values in my column that is either positive or negative.  The + and - figures will always balance out at the end. Is there a way where I can take this column and create 2 separate columns for one to hold positive values and the other negative values.

Example -->

[m_woolf · Accepted solution]

Load

       Project,
       if(Values<0,Values) as Negative,
       if(Values>00,Values) as Positive
From ........

[pradosh_thakur]

tab:
load * Inline [
Project,Values
abc,100
abc,-100
def,200
def,-200
ghi,300
ghi,-300
];

load Project, Values as Positive

resident tab where Values >0;
inner join
load Project, Values as Negative

resident tab where Values <0;

drop Table tab;

[pradosh_thakur]

tab:
load * Inline [
Project,Values
abc,100
abc,-100
def,200
def,-200
ghi,300
ghi,-300
];

load Project, Values as Positive

resident tab where Values >0;
inner join
load Project, Values as Negative

resident tab where Values <0;

drop Table tab;

[dreweezy]

Thanks for your answer. To clarify, I will have thousands of rows. Would I be able to do a for loop within the load script in order to avoid manually entering values as you have mentioned. 

Thanks.

[pradosh_thakur]

Yes. I have to send the script that's why the inline load. Is this what you were looking for?

[dreweezy]

Something similar I will try to write a for loop to see if that will bring in the values to separate between positive and negative values. 

[pradosh_thakur]

Ohk .. Please post the answer once you get it and close the thread so that it can help others.

[dreweezy]

update - instead of writing a forloop I created a bucket to denote whether the values inside column 'A' was a 'Positive' or 'Negative'. Once I had this in place, I was able to simply drag these 2 new columns into a pivot table against a dimension and show all negative values on one column and positive on another. 

[pradosh_thakur]

Great. Please close the thread by marking correct and helpful answers.