After Creating a new column how do you move it so it becomes the first Column using a Script?

[#1]

Hi Everyone,

Background:

So I'm trying to automate a process I use to retrieve sample data from a SQL database. What I do is use the database query builder and extract data with a Post-Query Script which is where the dummy code below will go.

Problem:

I have currently got the script to create a new column and give it the formulas I want, but what would be really useful is having some lines of code to then shuffle some of these new columns around.

My Dummy code is as follows:

"

New Column( "ColumnToMove",
  Numeric,
  "Nominal",
  Format( "Best", 12 )
  Formula(1));
 
Move Selected Columns({“ColumnToMove”}, To First);

"

(Please forgive any misunderstandings of how this scripting language works, I'm currently very new to it)

Many thanks for any help given

[#2 · Accepted solution]

I think you can either use add multiple columns to set the position when you create it or use the move selected columns message you mention:

dt=currentdatatable();
dt<<add multiple columns("Dummy",1,"Before First",numeric);
dt:columntomove<<set selected(1);
dt<<move selected columns("To First")<<clear column selection;

You can specify the location with After(col) for both methods

[#3]

Moving columns is possible but it always feels like it takes two or three extra steps then it should.  Mainly, you can only move selected columns, and it's not easy to select columns.

Names Default To Here( 1 );
dt = Open( "$SAMPLE_DATA/Big Class.jmp" );
dt << Go To( :Age );
Wait( 2 );
dt << Move Selected Columns( To last );
dt << Clear Column Selection;
Wait( 2 );
dt << Reverse Order;

Is there any reason you can't just modify your SQL to get the columns in the order you want?  SQL is a robust enough language you can do most column scripting right in the data pull.

[#4]

If you provide the column names to move selected columns you don't have to select them in order to move them.

dt = New Table( "Untitled", Add Rows( 1 ),
      New Column( "First", Numeric, "Continuous", Format( "Best", 12 ),
            Set Values( [1] ) ),
      New Column( "Second", Numeric, "Continuous", Format( "Best", 12 ),
            Set Values( [2] ) )
);
 
dt << New Column( "ColumnToMove", Numeric, "Nominal", Format( "Best", 12 ), Formula(1));
wait(2);    // Put a delay to see the effect
dt << Move Selected Columns({"ColumnToMove"}, To First);

[#5]

Awesome tip. 

I generally never move columns.  Ordering columns is more a matter of presentation since data shouldn't be column order dependent.  It's best to leave presentation to a presentation layer like a graph or chart and not a data table.

[#6]

BTW: If you haven't already found it, the Scripting Index under the Help menu is your friend.

[#7]

Thanks for all your help everyone, really appreciate it. Got my answer and more

(msharp - the reason I'm not using SQL to do more of the work is because I'm using the Query builder to get my data from SQL and I don't believe you can make further adjustments to the SQL that it is using. Now I could script my whole query into the scripting language and do all of the work within SQL rather than JMP. However I find it quite useful being able to select different variables using the query builder each time before extracting them (rather than hard coding them and having to find the entries in the script each time). Equally I may be missing something though.

Also I agree about the presentation layer, but currently I'm finding it quite useful at the moment while I'm getting to grips with things to organise as I go. Also I have quite a number of columns (my next step will be to group them using the scripting language) which means that when it comes to data exploration (which is currently what I'm doing) it's useful having the more relevant columns closer to hand. Also when I come to use graph builder etc. it makes the data exploration a bit easier. Thanks again for all your help)

(Jeff Perkinson - No I did not know about that. Thank you very much for bringing that to my attention. It will be very used from now on)

[#8]

There's nothing wrong with ordering columns, and it's great to keep things organized when you code.  However, it CAN slow down your code.  The obvious is it's adding an extra step that doesn't actually do anything.  Reordering an invisible table will be faster, but then again, the tables invisible so reordering serves no purpose. 

In addition, a novice programmer also could get in the habit of reordering columns to run such and such an analysis on the first 5 columns, or last 5 columns, which is a bad habit and produces less flexible slower code.

Lastly, choosing between adding columns with SQL or JSL is a personal opinion.  Obviously both can add an extra column where Column 3 = Column 1 + Column 2.  With SQL this transfers the processing power from the host computer to the server CPU, which could be slower/faster but will be more consistent across platforms.  With SQL comes the added benefit of being able to place that column where-ever you want without adding an extra step or two.  This all being said, I often prefer using JSL to add columns.