51666_en-US

liliconcom

Copy values from adjacent rows to a new table

I have a table having two columns, say for example Name and Salary. I want to extract salaries of everyone whose names are, say, 'Hannah'. These extracted values should then make a new table and the values should go under column 'Hannah'.

Any help will be appreciated. Thanks

Re: Copy values from adjacent rows to a new table

Thank you. I did change the character value to numeric as per my data. It works. I really appreciate your help. Thanks a ton

Re: Copy values from adjacent rows to a new table

@txnelson, 
      No , you are correct. I am going to edit and make the correction. 

Re: Copy values from adjacent rows to a new table

Given the chance that I might be stepping on @uday_guntupalli toes, I am requesting that you remove the Solution checkmarks from his scripts, because they are incorrect and will not work properly.

See below for an evaluation of the code

// This code works correctly
dt = Current Data Table(); // Assuming you have a data table open 

dt << Select Where(:Name == "Hannah"); 

dt1 = dt << Subset(Selected Rows(1), Selected Columns(0)); 

HannahSalary = dt1:Salary << Get Values; 

dt2 = New Table(); 

// This code will not work.  
// The new column is declared as "Character".  The set values is a numeric value and 
// will not enter the data properly.  It needs to be specified as "Numeric"
dt2 << New Column("HannahsSalary",Character, Continuous, << Set Values(HannahSalary)); 

// This code will not work for the same reason as above, and in addition,
// it places the Salary value on the first n rows of the data table, no matter
// where the rows for "Hannah" are. 
dt << New Column("HannahsSalary",Character, Continuous, << Set Values(HannahSalary));

Re: Copy values from adjacent rows to a new table

This is perfect.. Thank you

Re: Copy values from adjacent rows to a new table

If you add this to the script I provided it will add a new column to the inital data table;

dt << New Column("Hannah",formula(If(dt:Name == "Hannah", dt:Salary)));

Re: Copy values from adjacent rows to a new table

@powerpuff, 
    Presuming the data table you want to add it to isopen : 

 

Correcting Data Type  ( In Edit) 

   

dt = Current Data Table() ; 

dt << New Column("HannahsSalary",Numeric,Continuous, << Set Values(HannahSalary)); 

 

 

Re: Copy values from adjacent rows to a new table

Hey Uday,

 

Also, can you tell me how I can add this column to an already existing table?

Re: Copy values from adjacent rows to a new table

That works, thanks a lot.

Re: Copy values from adjacent rows to a new table

@powerpuff, 
      Something like this  ? This is an example to explain an approach - if you want exact code - can either implement the same on a sample data or provide a subset of your table 

 

Edited - Correcting Data Type 

 

dt = Current Data Table(); // Assuming you have a data table open 

dt << Select Where(:Name == "Hannah"); 

dt1 = dt << Subset(Selected Rows(1), Selected Columns(0)); 

HannahSalary = dt1:Salary << Get Values; 

dt2 = New Table(); 

dt2 << New Column("HannahsSalary",Numeric,Continuous, << Set Values(HannahSalary)); 

 

Re: Copy values from adjacent rows to a new table

I think this should work

Names Default To Here( 1 );
dt = Current Data Table();

dt << select where( :Name == "Hannah" );

dt2 = dt << subset( selected rows( 1 ), columns( "Salary" ) );

dt2:Salary << set name( "Hannah" );