42871_en-US

liliconcom

import: a lot of extra col's. User select the ones they want?

Good morning,

So I'm new to jsl and trying to figure this out.  The data sets I'm getting from our servers have a ton of extra columns, often col's that are blank.  I'm hoping to start most of my scripts with a user selected dialogue box allowing them to select/add/drop columns of their choice and possibly rearrange them into a preferred order?  Any help?

 

Let's say the col's are:

 

Col#	ColName
1	date_export
2	product_type
3	part_Number
4	recipe
5	date_run
6	measurement_type
7	Value
8	Tool
9	Target

 

OK, so some of these col's aren't needed and they all are definitely out of order, just from a human viewing perspective.  Ideally, I'd like the script to autoselect and order something like:

Col#:  2, 8, 4, 5, 6, 7, 9

and then maybe have a user dialogue box allowing user to select any other column to add to the reformatted output if they desire.  

 

what do you think?

Re: import: a lot of extra col's. User select the ones they want?

Presuming I understand your question : 

// Open Sample Data Table 
dt = Open( "$SAMPLE_DATA\Air Traffic.jmp" ); 

// Define list of columns to delete 
DelList = {"Carrier Code","Flight Number"}; 

// Delete Columns 
dt << Delete Columns(DelList); 

Re: import: a lot of extra col's. User select the ones they want?

awesome!  what about a pre-determined list of column names?  Like my data tables come with dozens or extra columns (often not empty).  

select colname (crap1, crap2, crap3...)

delete selected columns

 

then go on to allow user selection of the trimmed down remainder?

Re: import: a lot of extra col's. User select the ones they want?

Here is a piece of code that I use to create a table script that will zap empty columns when I'm reading data in:

 

 // add a table script that will remove empty columns
 dt << Set Property( "Remove Empty Columns",
  dt = Current Data Table();
  lstToDelete = {};
  For (i=1,i<=NCols(dt),i++,
   col = Column(dt,i);
   If (Col Number(col)==0,
    InsertInto(lstToDelete,i)
   )
  );
  If (NItems(lstToDelete)>0,
   dt << Delete Columns(lstToDelete)
  );  
 );

Re: import: a lot of extra col's. User select the ones they want?

I like this a lot, selecting the desired col's, then inverting it and deleting the undesired col's.

 

Do you have advice for running a prestep, where I can do a mass 'delete' of pre-specified col's to trim down the user selection?  

Unfortunately, my data files come with a few dozen unnecessary columns for things that aren't needed.

Maybe something I could just write in to the start of every dt=open(), to delete the known 'unneeded cols'?

Re: import: a lot of extra col's. User select the ones they want?

dt = Open( "$SAMPLE_DATA\Big Class.jmp" ); 

// Get Column Names 
ColNames = dt << Get Column Names("String");

// Open a window  for user selection 
NW1 = New Window("User Selection",
     << Modal, 
     V List Box(
      Lineup Box(
        Lb1 = List Box(ColNames);
          )
         ),
     H List Box( 
      Button Box("OK",
         // Unload User Inputs
         Rb1Val = Lb1 << Get Selected(); 
           )
         )   
                );
             
// Select Columns 
for(i = 1, i <= N Items(ColNames), i ++, 
  If(Contains(Rb1Val,ColNames[i]),
    Col = Column(dt,i); ;
    Col << Set Selected(1);
    );
   );
   
// Delete Unwanted Columns 
dt << Invert Column Selection ;
dt << Delete Columns ;

Re: import: a lot of extra col's. User select the ones they want?

Hopefully the nature of the foreign file format is stable. You might try using this argument to the Open() function for importing:

Select Columns( "col", ... )

 

Assuming that you stored the data reference in a variable (such as dt) when it opened, you can then iteratively re-arrange the columns using this pattern:

dt << Go to Column( col ref )  // select column to move
 << Move Selected Columns( ... ) // move column to new location
 << Clear Column Selection;  // 

Hope this reply gets you moving on your way.

 

Re: import: a lot of extra col's. User select the ones they want?

Hey Mark, 

 

All good questions.  I wish I could just script a query on the front end, but my company's DB are locked down.  We have to put in requests and we get out a flat excel .csv file that has all these unnecessary columns and poorly named headers in a mismatched order (like 35 columns).  I'm working on getting read-only access, but that's a different problem.

Re: import: a lot of extra col's. User select the ones they want?

Are the source files flat or relational? What is the source file type? What kind of files are you importing? Do you have to import the entire file?

Can you query a database for the result? If so, you can build a query to give you what you want.