BookmarkSubscribe
Choose Language Hide Translation Bar
Highlighted
himajafeb8
Community Trekker

loop through column names which has same string and create subset

Hi,

I have a JMP table "Sheet 1" with 300 columns. I am trying to create subset for all same columns which has a string in column name as "Ahu1" and then make changes to those subset like adding the columns,updating them and generating graphs and finally saving them in a folder. I was able to write script for "Ahu1". But i have to do the same for all other "Ahu's" . I want this process to be repeated for other column names containing stings:  which are Ahu2,Ahu3,Ahu4,Ahu5,Ahu6...Ahu52. How to do this in a for loop?  

The Script is below.

 

 
original_dt = Current Data Table();
Data Table( "Sheet1" ) << Subset(
	Output Table( "Ahu1", link to original data table( 0 ) ),
	All rows,
	columns(
		:Timestamp,
		:donSteedOccupancy,
		:Name( "Raeford, NC Temp" ),
		:DonSteedES Ahu1 ClgVlvCmd,
		:DonSteedES Ahu1 HtgVlvCmd,
		:DonSteedES Ahu1 DaT,
		:DonSteedES Ahu1 SaFanCmd,
		:DonSteedES Ahu1 ZnT,
		:DonSteedES Ahu1 ZnTStpt
	)
);
Ahu1_dt = Data Table( "Ahu1" );
Ahu1_dt << New Column( "Occupancy",
	character,
	Nominal,
	Formula( Munger( :donSteedOccupancy, Length( :donSteedOccupancy ) - 4, Length( :donSteedOccupancy ) ) )
);
Column( "Occupancy" ) << delete formula;
Ahu1_dt << delete columns( "donSteedOccupancy" );
Ahu1_dt << New Column( "SaFanCmd",
	character,
	Nominal,
	Formula(
		Munger(
			:DonSteedES Ahu1 SaFanCmd,
			Length( :DonSteedES Ahu1 SaFanCmd ) - 4,
			Length( :DonSteedES Ahu1 SaFanCmd )
		)
	)
);
Column( "SaFanCmd" ) << delete formula;
Ahu1_dt << delete columns( "DonSteedES Ahu1 SaFanCmd" );
Ahu1_dt << Column( "Occupancy" );

For Each Row( :Occupancy = Match( :Occupancy, " 1min", " 1", " 0min", " 0", :Occupancy ) );

Column( "Occupancy" ) << Data type( character ) << Set Data Type( Numeric );
Ahu1_dt << Column( "SaFanCmd" );

For Each Row( :SaFanCmd = Match( :SaFanCmd, " 1min", " 1", " 0min", " 0", :SaFanCmd ) );

Column( "SaFanCmd" ) << Data type( character ) << Set Data Type( Numeric );
Ahu1_dt:DonSteedES Ahu1 ClgVlvCmd << Format( "percent" );
Ahu1_dt:DonSteedES Ahu1 HtgVlvCmd << Format( "percent" );
Column( "SaFanCmd" ) << Modeling type( continuous );

optstartgraph = Ahu1_dt << Graph Builder(
	Size( 1377, 923 ),
	Variables(
		X( :Timestamp ),
		Y( :DonSteedES Ahu1 ClgVlvCmd ),
		Y( :DonSteedES Ahu1 HtgVlvCmd, Position( 1 ) ),
		Y( :Name( "Raeford, NC Temp" ) ),
		Y( :DonSteedES Ahu1 DaT, Position( 2 ) ),
		Y( :DonSteedES Ahu1 ZnT, Position( 2 ) ),
		Y( :DonSteedES Ahu1 ZnTStpt, Position( 2 ) ),
		Y( :SaFanCmd, Position( 2 ), Side( "Right" ) ),
		Color( :Occupancy )
	),
	Elements( Position( 1, 1 ), Line( X, Y( 1 ), Y( 2 ), Color( 0 ), Legend( 5 ) ), Heatmap( X, Legend( 8 ) ) ),
	Elements(
		Position( 1, 2 ),
		Line( X, Y( 1 ), Y( 2 ), Y( 3 ), Y( 4 ), Color( 0 ), Legend( 6 ) ),
		Line( X, Y( 5 ), Color( 0 ), Legend( 7 ) ),
		Heatmap( X, Legend( 9 ) )
	),
	SendToReport(
		Dispatch( {"Line[DonSteedES Ahu1 ClgVlvCmd...]"}, "", OutlineBox, {Close( 0 )} ),
		Dispatch( {"Heatmap[DonSteedES Ahu1 ClgVlvCmd...]"}, "", OutlineBox, {Close( 0 )} ),
		Dispatch( {"Line[Raeford, NC Temp...]"}, "", OutlineBox, {Close( 0 )} ),
		Dispatch( {"Line[SaFanCmd] (right)"}, "", OutlineBox, {Close( 0 )} ),
		Dispatch( {"Heatmap[Raeford, NC Temp...]"}, "", OutlineBox, {Close( 0 )} ),
		Dispatch(
			{},
			"Timestamp",
			ScaleBox,
			{Min( 3537633600 ), Max( 3538263600 ), Interval( "Hour" ), Inc( 1 ), Minor Ticks( 0 ),
			Label Row( Label Orientation( "Angled" ) )}
		),
		Dispatch( {}, "DonSteedES Ahu1 ClgVlvCmd", ScaleBox, {Min( 0 )} ),
		Dispatch(
			{},
			"400",
			ScaleBox,
			{Legend Model(
				8,
				Properties(
					0,
					{Transparency( 0.35 ), gradient(
						{Color Theme(
							{"mine", 16387, {{79, 16, 79}, {119, 0, 119}, {42, 63, 255}, {0, 170, 170}, {0, 227,
							0}, {221, 221, 0}, {238, 119, 0}, {252, 11, 11}, {158, 33, 33}}, {0,
							0.193732193732194, 0.2, 0.495726495726496, 0.501424501424502, 0.507122507122507,
							0.943019943019943, 0.95, 0.954415954415954}}
						)}
					)}
				)
			), Legend Model(
				9,
				Properties(
					0,
					{Transparency( 0.35 ), gradient(
						{Color Theme(
							{"mine", 16387, {{79, 16, 79}, {119, 0, 119}, {42, 63, 255}, {0, 170, 170}, {0, 227,
							0}, {221, 221, 0}, {238, 119, 0}, {252, 11, 11}, {158, 33, 33}}, {0,
							0.193732193732194, 0.2, 0.495726495726496, 0.501424501424502, 0.507122507122507,
							0.943019943019943, 0.95, 0.954415954415954}}
						)}
					)}
				)
			)}
		),
		Dispatch(
			{},
			"400",
			LegendBox,
			{Legend Position( {5, [0, 1], 8, [2], 6, [-3, -3, 4, 5], 7, [-3], 9, [3]} ),
			Position( {0, 1, 2, -3, -3, 4, 5, -3, 3} )}
		)
	)
);
optstartgraph << Save Picture( "C:\Projects\Projects\Hoke\Year 1\Jmp\Year 0\DonSteed\Feb16\Ahu1.jpg", "JPG" );
optstartgraph << close window;

 

Please help me out.

 

Thanks.

 

 

0 Kudos
1 ACCEPTED SOLUTION

Accepted Solutions
txnelson
Super User

Re: loop through column names which has same string and create subset

Here is a version of your code that will loop across all 50 of your Ahu column sets.  See the comments in the script as to what was done to make the looping possible

Names Default To Here( 1 );
original_dt = Data Table( "Sheet1" );

// Loop across all 50 Ahu column sets
// The number of column sets could be programatically determined
// if desired
For( i = 1, i <= 50, i++,

	// Set the Ahu version to be used
	targetAhu = "Ahu" || Char( i );
	
	// Create the subsetted data table, naming it by the 
	// name of the current Ahu version
	Ahu_dt = original_dt << Subset(
		Output Table( targetAhu, link to original data table( 0 ) ),
		All rows,
		columns(
			original_dt:Timestamp,
			original_dt:donSteedOccupancy,
			original_dt:Name( "Raeford, NC Temp" ),
			Column( original_dt, "DonSteedES " || targetAhu || " ClgVlvCmd" ),
			Column( original_dt, "DonSteedES " || targetAhu || " HtgVlvCmd" ),
			Column( original_dt, "DonSteedES " || targetAhu || " DaT" ),
			Column( original_dt, "DonSteedES " || targetAhu || " SaFanCmd" ),
			Column( original_dt, "DonSteedES " || targetAhu || " ZnT" ),
			Column( original_dt, "DonSteedES " || targetAhu || " ZnTStpt" )
		)
	);
	
	// no need for this statement, since it is set when the data table is created
	//Ahu_dt = Data Table( targetAhu );
	
	// No changes needed here, except for pointing to the current Ahu data table
	Ahu_dt << New Column( "Occupancy",
		character,
		Nominal,
		Formula( Munger( :donSteedOccupancy, Length( :donSteedOccupancy ) - 4, Length( :donSteedOccupancy ) ) )
	);
	Column( Ahu_dt, "Occupancy" ) << delete formula;
	Ahu_dt << delete columns( "donSteedOccupancy" );

	// This section of code has required changes embedded within a formula
	// JMP will not resolve the code properly and therefore, Substitution
	// is required to force the generation of the exact code required for
	// the formula
	Eval(
		Substitute(
				Expr(
					Ahu_dt << New Column( "SaFanCmd",
						character,
						Nominal,
						Formula( Munger( __SaFanCmd__, Length( __SaFanCmd__ ) - 4, Length( __SaFanCmd__ ) ) )
					)
				),
			Expr( __SaFanCmd__ ), Parse( ":DonSteedES " || targetAhu || " SaFanCmd" )
		)
	);
	Column( Ahu_dt, "SaFanCmd" ) << delete formula;

	// Minor changes, just making sure the current target data table is being
	// pointed to
	Ahu_dt << delete columns( "DonSteedES " || targetAhu || " SaFanCmd" );
	Ahu_dt << Column( "Occupancy" );

	For Each Row( ahu_dt:Occupancy = Match( Ahu_dt:Occupancy, " 1min", " 1", " 0min", " 0", Ahu_dt:Occupancy ) );

	// I don't understand this next line of code....changing to character and then changing to numeric
	// unless you are using JMP to get rid of non numeric data
	Column( ahu_dt, "Occupancy" ) << Data type( character ) << Set Data Type( Numeric );
	Ahu_dt << Column( "SaFanCmd" );

	For Each Row( ahu_dt:SaFanCmd = Match( ahu_dt:SaFanCmd, " 1min", " 1", " 0min", " 0", ahu_dt:SaFanCmd ) );

	Column( ahu_dt, "SaFanCmd" ) << Data type( character ) << Set Data Type( Numeric );

	Column( Ahu_dt, "DonSteedES " || targetAhu || " ClgVlvCmd" ) << Format( "percent" );
	Column( Ahu_dt, "DonSteedES " || targetAhu || " HtgVlvCmd" ) << Format( "percent" );
	Column( Ahu_dt, "SaFanCmd" ) << Modeling type( continuous );

	// The Graph Builder Platform has a few places where code will not be resolved, and therefore
	// once again Substitution() is what is needed to make it work
	Eval(
		Substitute(
				Expr(
					optstartgraph = Ahu_dt << Graph Builder(
						Size( 1377, 923 ),
						Variables(
							X( :Timestamp ),
							Y( Column( Ahu_dt, "DonSteedES " || targetAhu || " ClgVlvCmd" ) ),
							Y( Column( Ahu_dt, "DonSteedES " || targetAhu || " HtgVlvCmd" ), Position( 1 ) ),
							Y( :Name( "Raeford, NC Temp" ) ),
							Y( Column( Ahu_dt, "DonSteedES " || targetAhu || " DaT" ), Position( 2 ) ),
							Y( Column( Ahu_dt, "DonSteedES " || targetAhu || " ZnT" ), Position( 2 ) ),
							Y( Column( Ahu_dt, "DonSteedES " || targetAhu || " ZnTStpt" ), Position( 2 ) ),
							Y( :SaFanCmd, Position( 2 ), Side( "Right" ) ),
							Color( :Occupancy )
						),
						Elements( Position( 1, 1 ), Line( X, Y( 1 ), Y( 2 ), Color( 0 ), Legend( 5 ) ), Heatmap( X, Legend( 8 ) ) ),
						Elements(
							Position( 1, 2 ),
							Line( X, Y( 1 ), Y( 2 ), Y( 3 ), Y( 4 ), Color( 0 ), Legend( 6 ) ),
							Line( X, Y( 5 ), Color( 0 ), Legend( 7 ) ),
							Heatmap( X, Legend( 9 ) )
						),
						SendToReport(
							Dispatch( {__LineClgVlvCmd__}, "", OutlineBox, {Close( 0 )} ),
							Dispatch( {__HeatmapClgVlvCmd__}, "", OutlineBox, {Close( 0 )} ),
							Dispatch( {"Line[Raeford, NC Temp...]"}, "", OutlineBox, {Close( 0 )} ),
							Dispatch( {"Line[SaFanCmd] (right)"}, "", OutlineBox, {Close( 0 )} ),
							Dispatch( {"Heatmap[Raeford, NC Temp...]"}, "", OutlineBox, {Close( 0 )} ),
							Dispatch(
								{},
								"Timestamp",
								ScaleBox,
								{Min( 3537633600 ), Max( 3538263600 ), Interval( "Hour" ), Inc( 1 ), Minor Ticks( 0 ),
								Label Row( Label Orientation( "Angled" ) )}
							),
							Dispatch( {}, "DonSteedES " || targetAhu || " ClgVlvCmd", ScaleBox, {Min( 0 )} ),
							Dispatch(
								{},
								"400",
								ScaleBox,
								{Legend Model(
									8,
									Properties(
										0,
										{Transparency( 0.35 ), gradient(
											{Color Theme(
												{"mine", 16387, {{79, 16, 79}, {119, 0, 119}, {42, 63, 255}, {0, 170, 170}, {0, 227, 0}, {221, 221, 0
												}, {238, 119, 0}, {252, 11, 11}, {158, 33, 33}}, {0, 0.193732193732194, 0.2, 0.495726495726496,
												0.501424501424502, 0.507122507122507, 0.943019943019943, 0.95, 0.954415954415954}}
											)}
										)}
									)
								), Legend Model(
									9,
									Properties(
										0,
										{Transparency( 0.35 ), gradient(
											{Color Theme(
												{"mine", 16387, {{79, 16, 79}, {119, 0, 119}, {42, 63, 255}, {0, 170, 170}, {0, 227, 0}, {221, 221, 0
												}, {238, 119, 0}, {252, 11, 11}, {158, 33, 33}}, {0, 0.193732193732194, 0.2, 0.495726495726496,
												0.501424501424502, 0.507122507122507, 0.943019943019943, 0.95, 0.954415954415954}}
											)}
										)}
									)
								)}
							),
							Dispatch(
								{},
								"400",
								LegendBox,
								{Legend Position( {5, [0, 1], 8, [2], 6, [-3, -3, 4, 5], 7, [-3], 9, [3]} ), Position(
									{0, 1, 2, -3, -3, 4, 5, -3, 3}
								)}
							)
						)
					)
				),
			Expr( __LineClgVlvCmd__ ), "Line[DonSteedES " || targetAhu || " ClgVlvCmd...]",
			Expr( __HeatmapClgVlvCmd__ ), "Heatmap[DonSteedES " || targetAhu || " ClgVlvCmd...]"
		)
	);
	
	// Save the current graphic, replacing the jpg name with the current target Ahu version
	optstartgraph << Save Picture( "C:\Projects\Projects\Hoke\Year 1\Jmp\Year 0\DonSteed\Feb16\" || targetAhu || ".jpg", "JPG" );
	optstartgraph << close window;
	// You can also close the current target data table if you want
	close( dt_Ahu, no save );
); // End of For Loop
Jim
9 REPLIES 9
uday_guntupalli
Community Trekker

Re: loop through column names which has same string and create subset

@himajafeb8
        A couple of suggestions, 

1. When you share code, please kindly use the "Insert JSL Script" or "Insert Code" option, so the code is formatted to enable others to easily read what you are sharing. 

2. Please break down your problem and explain which part of the code is actually giving you issues. 

 

       Going by your title, I would think doing something like the following would work. However, if you have more questions, request you to kindly break it down : 

 

Clear Log(); Clear Globals(); 

// Open Sample Data 
dt = Open( "$SAMPLE_DATA/Football.jmp" );

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

// Select Desired Columns 
DesTerm = "Speed";
DesCols = {}; // Initialize empty list to store selected columns 
for(i = 1 , i <= N Items(ColNames), i++,
	 If(Contains(ColNames[i],DesTerm),
			Insert Into(DesCols,i); 
			Col = Column(dt,i); 
			Col << Set Selected(1);
	   );
   );

// Subset 
dt1 = dt << Subset(Selected Rows(0),Selected Columns(1)); 
Best
Uday
0 Kudos
himajafeb8
Community Trekker

Re: loop through column names which has same string and create subset

@uday_guntupalli Thanks for all the Suggestions. I will keep in my mind before posting anything .

 

I have posted my code below and it's working absolutely fine. But I need to run this code in a loop.

 

I have my original data table "Sheet 1". It has 300 columns like ("Timestamp" , "donsteedOccupancy" ,  "Name( "Raeford, NC Temp" )" , "x Ahu1 y" , "x Ahu1 z" , "x Ahu1 m" ,..."x Ahu2 y" , "x Ahu2 z" , "x Ahu2 m"......."x Ahu70 y" , "x Ahu70 z" , "x Ahu70 m".

 

I want to create a subset from main datatable with first 3 columns ("Timestamp" , "donsteedOccupancy" ,  "Name( "Raeford, NC Temp" )" ) and also  all the column names consisting "Ahu1" to a new data table and perform various actions as i mentioned in the code.(Creating extra columns,deleting columns,Change Column properties and generating a graph).

 

So bascially in this code were there is "Ahu1" it has to be replaced to "Ahu[i]" and run in the loop.

 

I want to do something like this.

 

 

For( i=0; i<= 52, i++
       Data Table("Sheet1") << subset(
       Output table( "Ahu[i]"), 
        columns(Timestamp,..., "x Ahu[i] y" , "x Ahu[i] z" , "x Ahu[i] m"));
Ahu[i]_dt= Data Table("Ahu[i]");
.
.
.
Close( Ahu[i]_dt, save("Ahu[i].jmp") );

 

 

 All the fields in the code which has "Ahu1" has to be replaced to "Ahu[i]"

 

Thanks for the help.

 

original_dt = Current Data Table();

/* Sheet 1 is my original data table. I am creating a subset from original sheet which has column names as : Timestamp,donsteedOccupancy, Name( "Raeford, NC Temp" )
 and all the columns which has a string "Ahu1". I am naming this output table as Ahu1.*/

Data Table( "Sheet1" ) << Subset(
	Output Table( "Ahu1", link to original data table( 0 ) ),
	All rows,
	columns(
		:Timestamp,
		:donSteedOccupancy,
		:Name( "Raeford, NC Temp" ),
		:DonSteedES Ahu1 ClgVlvCmd,
		:DonSteedES Ahu1 HtgVlvCmd,
		:DonSteedES Ahu1 DaT,
		:DonSteedES Ahu1 SaFanCmd,
		:DonSteedES Ahu1 ZnT,
		:DonSteedES Ahu1 ZnTStpt
	)
);

/* Assigning the output data table to "Ahu1_dt" */

Ahu1_dt = Data Table( "Ahu1" );

/* Creating new columns called "Occupancy and SafanCmd" with formula's and deleting the formula link and also the original column's  */

Ahu1_dt << New Column( "Occupancy",
	character,
	Nominal,
	Formula( Munger( :donSteedOccupancy, Length( :donSteedOccupancy ) - 4, Length( :donSteedOccupancy ) ) )
);

Column( "Occupancy" ) << delete formula;

Ahu1_dt << delete columns( "donSteedOccupancy" );

Ahu1_dt << New Column( "SaFanCmd",
	character,
	Nominal,
	Formula( Munger( :DonSteedES Ahu1 SaFanCmd, Length( :DonSteedES Ahu1 SaFanCmd ) - 4, Length( :DonSteedES Ahu1 SaFanCmd ) ) )
);

Column( "SaFanCmd" ) << delete formula;

Ahu1_dt << delete columns( "DonSteedES Ahu1 SaFanCmd" );

/* Using Recode to change values for columns "Occupancy and SaFanCmd " and also changing the column Data Types*/

Ahu1_dt << Column( "Occupancy" );
	
For Each Row( :Occupancy = Match( :Occupancy, " 1min", " 1", " 0min", " 0", :Occupancy ) );
	
Column( "Occupancy" ) << Data type( character ) << Set Data Type( Numeric );

Ahu1_dt << Column( "SaFanCmd" );
	
For Each Row( :SaFanCmd = Match( :SaFanCmd, " 1min", " 1", " 0min", " 0", :SaFanCmd ) );
	
Column( "SaFanCmd" ) << Data type( character ) << Set Data Type( Numeric );

/* Changing Format for the column's "DonSteedES Ahu1 ClgVlvCmd" and "Ahu1_dt:DonSteedES Ahu1 HtgVlvCmd" */

Ahu1_dt:DonSteedES Ahu1 ClgVlvCmd << Format( "percent" );

Ahu1_dt:DonSteedES Ahu1 HtgVlvCmd << Format( "percent" );

/* Changing Modeling type for the column : SaFanCmd */

Column( "SaFanCmd" ) << Modeling type( continuous );

/* Generating a Graph using Graph Builder for "Ahu1_dt" and assigning it to a object called "optstartgraph" .*/

optstartgraph = Ahu1_dt << Graph Builder(
	Size( 1377, 923 ),
	Variables(
		X( :Timestamp ),
		Y( :DonSteedES Ahu1 ClgVlvCmd ),
		Y( :DonSteedES Ahu1 HtgVlvCmd, Position( 1 ) ),
		Y( :Name( "Raeford, NC Temp" ) ),
		Y( :DonSteedES Ahu1 DaT, Position( 2 ) ),
		Y( :DonSteedES Ahu1 ZnT, Position( 2 ) ),
		Y( :DonSteedES Ahu1 ZnTStpt, Position( 2 ) ),
		Y( :SaFanCmd, Position( 2 ), Side( "Right" ) ),
		Color( :Occupancy )
	),
	Elements( Position( 1, 1 ), Line( X, Y( 1 ), Y( 2 ), Color( 0 ), Legend( 5 ) ), Heatmap( X, Legend( 8 ) ) ),
	Elements(
		Position( 1, 2 ),
		Line( X, Y( 1 ), Y( 2 ), Y( 3 ), Y( 4 ), Color( 0 ), Legend( 6 ) ),
		Line( X, Y( 5 ), Color( 0 ), Legend( 7 ) ),
		Heatmap( X, Legend( 9 ) )
	),
	SendToReport(
		Dispatch( {"Line[DonSteedES Ahu1 ClgVlvCmd...]"}, "", OutlineBox, {Close( 0 )} ),
		Dispatch( {"Heatmap[DonSteedES Ahu1 ClgVlvCmd...]"}, "", OutlineBox, {Close( 0 )} ),
		Dispatch( {"Line[Raeford, NC Temp...]"}, "", OutlineBox, {Close( 0 )} ),
		Dispatch( {"Line[SaFanCmd] (right)"}, "", OutlineBox, {Close( 0 )} ),
		Dispatch( {"Heatmap[Raeford, NC Temp...]"}, "", OutlineBox, {Close( 0 )} ),
		Dispatch(
			{},
			"Timestamp",
			ScaleBox,
			{Min( 3537633600 ), Max( 3538263600 ), Interval( "Hour" ), Inc( 1 ), Minor Ticks( 0 ), Label Row( Label Orientation( "Angled" ) )}
		),
		Dispatch( {}, "DonSteedES Ahu1 ClgVlvCmd", ScaleBox, {Min( 0 )} ),
		Dispatch(
			{},
			"400",
			ScaleBox,
			{Legend Model(
				8,
				Properties(
					0,
					{Transparency( 0.35 ), gradient(
						{Color Theme(
							{"mine", 16387, {{79, 16, 79}, {119, 0, 119}, {42, 63, 255}, {0, 170, 170}, {0, 227, 0}, {221, 221, 0}, {238, 119, 0},
							{252, 11, 11}, {158, 33, 33}}, {0, 0.193732193732194, 0.2, 0.495726495726496, 0.501424501424502, 0.507122507122507,
							0.943019943019943, 0.95, 0.954415954415954}}
						)}
					)}
				)
			), Legend Model(
				9,
				Properties(
					0,
					{Transparency( 0.35 ), gradient(
						{Color Theme(
							{"mine", 16387, {{79, 16, 79}, {119, 0, 119}, {42, 63, 255}, {0, 170, 170}, {0, 227, 0}, {221, 221, 0}, {238, 119, 0},
							{252, 11, 11}, {158, 33, 33}}, {0, 0.193732193732194, 0.2, 0.495726495726496, 0.501424501424502, 0.507122507122507,
							0.943019943019943, 0.95, 0.954415954415954}}
						)}
					)}
				)
			)}
		),
		Dispatch(
			{},
			"400",
			LegendBox,
			{Legend Position( {5, [0, 1], 8, [2], 6, [-3, -3, 4, 5], 7, [-3], 9, [3]} ), Position( {0, 1, 2, -3, -3, 4, 5, -3, 3} )}
		)
	)
);

/*Saving the object in jpg format to a specfied file location. */

optstartgraph << Save Picture( "C:\Projects\Projects\Hoke\Year 1\Jmp\Year 0\DonSteed\Feb16\Ahu1.jpg", "JPG" );

/* Closing the object */

optstartgraph << close window;

/* close and save the data table "Ahu1_dt" */

Close( Ahu1_dt, save("C:\Projects\Projects\Hoke\Year 1\Jmp\Year 0\DonSteed\Feb16\Ahu1.jmp") );

 

 

0 Kudos
uday_guntupalli
Community Trekker

Re: loop through column names which has same string and create subset

@himajafeb8
 Can you share a sample of your data ? 

Best
Uday
0 Kudos
himajafeb8
Community Trekker

Re: loop through column names which has same string and create subset

@uday_guntupalli

I have attached the sample data.

0 Kudos
uday_guntupalli
Community Trekker

Re: loop through column names which has same string and create subset

@himajafeb8

        If you were to try something like this - essentially use regular expressions to try and match Ahu on a loop every single time but edit the conditions to increment from 1 - n, I would think it would work. I dont have a working example. I will try a little more on the regex front 

dt_Raw = Current Data Table(); 
ColNames = dt_Raw << Get Column Names("String"); 
RegexToMatch = "^(a|A)hu[0-9]$";

for(i = 1 , i <= N Items(ColNames), i++,
		If(!IsMissing(Regex(Char(ColNames[i]),RegexToMatch)),
			Col = Column(dt_Raw,i);
			Col << Set Selected(1); 
		  );
   );
Best
Uday
0 Kudos
himajafeb8
Community Trekker

Re: loop through column names which has same string and create subset

@uday_guntupalli

 

Thats not working. You have any other way to do it?

 

thanks

0 Kudos
uday_guntupalli
Community Trekker

Re: loop through column names which has same string and create subset

@himajafeb8
      At this point, I couldn't get it to work. I would contact support@jmp.com . 

Best
Uday
txnelson
Super User

Re: loop through column names which has same string and create subset

Here is a version of your code that will loop across all 50 of your Ahu column sets.  See the comments in the script as to what was done to make the looping possible

Names Default To Here( 1 );
original_dt = Data Table( "Sheet1" );

// Loop across all 50 Ahu column sets
// The number of column sets could be programatically determined
// if desired
For( i = 1, i <= 50, i++,

	// Set the Ahu version to be used
	targetAhu = "Ahu" || Char( i );
	
	// Create the subsetted data table, naming it by the 
	// name of the current Ahu version
	Ahu_dt = original_dt << Subset(
		Output Table( targetAhu, link to original data table( 0 ) ),
		All rows,
		columns(
			original_dt:Timestamp,
			original_dt:donSteedOccupancy,
			original_dt:Name( "Raeford, NC Temp" ),
			Column( original_dt, "DonSteedES " || targetAhu || " ClgVlvCmd" ),
			Column( original_dt, "DonSteedES " || targetAhu || " HtgVlvCmd" ),
			Column( original_dt, "DonSteedES " || targetAhu || " DaT" ),
			Column( original_dt, "DonSteedES " || targetAhu || " SaFanCmd" ),
			Column( original_dt, "DonSteedES " || targetAhu || " ZnT" ),
			Column( original_dt, "DonSteedES " || targetAhu || " ZnTStpt" )
		)
	);
	
	// no need for this statement, since it is set when the data table is created
	//Ahu_dt = Data Table( targetAhu );
	
	// No changes needed here, except for pointing to the current Ahu data table
	Ahu_dt << New Column( "Occupancy",
		character,
		Nominal,
		Formula( Munger( :donSteedOccupancy, Length( :donSteedOccupancy ) - 4, Length( :donSteedOccupancy ) ) )
	);
	Column( Ahu_dt, "Occupancy" ) << delete formula;
	Ahu_dt << delete columns( "donSteedOccupancy" );

	// This section of code has required changes embedded within a formula
	// JMP will not resolve the code properly and therefore, Substitution
	// is required to force the generation of the exact code required for
	// the formula
	Eval(
		Substitute(
				Expr(
					Ahu_dt << New Column( "SaFanCmd",
						character,
						Nominal,
						Formula( Munger( __SaFanCmd__, Length( __SaFanCmd__ ) - 4, Length( __SaFanCmd__ ) ) )
					)
				),
			Expr( __SaFanCmd__ ), Parse( ":DonSteedES " || targetAhu || " SaFanCmd" )
		)
	);
	Column( Ahu_dt, "SaFanCmd" ) << delete formula;

	// Minor changes, just making sure the current target data table is being
	// pointed to
	Ahu_dt << delete columns( "DonSteedES " || targetAhu || " SaFanCmd" );
	Ahu_dt << Column( "Occupancy" );

	For Each Row( ahu_dt:Occupancy = Match( Ahu_dt:Occupancy, " 1min", " 1", " 0min", " 0", Ahu_dt:Occupancy ) );

	// I don't understand this next line of code....changing to character and then changing to numeric
	// unless you are using JMP to get rid of non numeric data
	Column( ahu_dt, "Occupancy" ) << Data type( character ) << Set Data Type( Numeric );
	Ahu_dt << Column( "SaFanCmd" );

	For Each Row( ahu_dt:SaFanCmd = Match( ahu_dt:SaFanCmd, " 1min", " 1", " 0min", " 0", ahu_dt:SaFanCmd ) );

	Column( ahu_dt, "SaFanCmd" ) << Data type( character ) << Set Data Type( Numeric );

	Column( Ahu_dt, "DonSteedES " || targetAhu || " ClgVlvCmd" ) << Format( "percent" );
	Column( Ahu_dt, "DonSteedES " || targetAhu || " HtgVlvCmd" ) << Format( "percent" );
	Column( Ahu_dt, "SaFanCmd" ) << Modeling type( continuous );

	// The Graph Builder Platform has a few places where code will not be resolved, and therefore
	// once again Substitution() is what is needed to make it work
	Eval(
		Substitute(
				Expr(
					optstartgraph = Ahu_dt << Graph Builder(
						Size( 1377, 923 ),
						Variables(
							X( :Timestamp ),
							Y( Column( Ahu_dt, "DonSteedES " || targetAhu || " ClgVlvCmd" ) ),
							Y( Column( Ahu_dt, "DonSteedES " || targetAhu || " HtgVlvCmd" ), Position( 1 ) ),
							Y( :Name( "Raeford, NC Temp" ) ),
							Y( Column( Ahu_dt, "DonSteedES " || targetAhu || " DaT" ), Position( 2 ) ),
							Y( Column( Ahu_dt, "DonSteedES " || targetAhu || " ZnT" ), Position( 2 ) ),
							Y( Column( Ahu_dt, "DonSteedES " || targetAhu || " ZnTStpt" ), Position( 2 ) ),
							Y( :SaFanCmd, Position( 2 ), Side( "Right" ) ),
							Color( :Occupancy )
						),
						Elements( Position( 1, 1 ), Line( X, Y( 1 ), Y( 2 ), Color( 0 ), Legend( 5 ) ), Heatmap( X, Legend( 8 ) ) ),
						Elements(
							Position( 1, 2 ),
							Line( X, Y( 1 ), Y( 2 ), Y( 3 ), Y( 4 ), Color( 0 ), Legend( 6 ) ),
							Line( X, Y( 5 ), Color( 0 ), Legend( 7 ) ),
							Heatmap( X, Legend( 9 ) )
						),
						SendToReport(
							Dispatch( {__LineClgVlvCmd__}, "", OutlineBox, {Close( 0 )} ),
							Dispatch( {__HeatmapClgVlvCmd__}, "", OutlineBox, {Close( 0 )} ),
							Dispatch( {"Line[Raeford, NC Temp...]"}, "", OutlineBox, {Close( 0 )} ),
							Dispatch( {"Line[SaFanCmd] (right)"}, "", OutlineBox, {Close( 0 )} ),
							Dispatch( {"Heatmap[Raeford, NC Temp...]"}, "", OutlineBox, {Close( 0 )} ),
							Dispatch(
								{},
								"Timestamp",
								ScaleBox,
								{Min( 3537633600 ), Max( 3538263600 ), Interval( "Hour" ), Inc( 1 ), Minor Ticks( 0 ),
								Label Row( Label Orientation( "Angled" ) )}
							),
							Dispatch( {}, "DonSteedES " || targetAhu || " ClgVlvCmd", ScaleBox, {Min( 0 )} ),
							Dispatch(
								{},
								"400",
								ScaleBox,
								{Legend Model(
									8,
									Properties(
										0,
										{Transparency( 0.35 ), gradient(
											{Color Theme(
												{"mine", 16387, {{79, 16, 79}, {119, 0, 119}, {42, 63, 255}, {0, 170, 170}, {0, 227, 0}, {221, 221, 0
												}, {238, 119, 0}, {252, 11, 11}, {158, 33, 33}}, {0, 0.193732193732194, 0.2, 0.495726495726496,
												0.501424501424502, 0.507122507122507, 0.943019943019943, 0.95, 0.954415954415954}}
											)}
										)}
									)
								), Legend Model(
									9,
									Properties(
										0,
										{Transparency( 0.35 ), gradient(
											{Color Theme(
												{"mine", 16387, {{79, 16, 79}, {119, 0, 119}, {42, 63, 255}, {0, 170, 170}, {0, 227, 0}, {221, 221, 0
												}, {238, 119, 0}, {252, 11, 11}, {158, 33, 33}}, {0, 0.193732193732194, 0.2, 0.495726495726496,
												0.501424501424502, 0.507122507122507, 0.943019943019943, 0.95, 0.954415954415954}}
											)}
										)}
									)
								)}
							),
							Dispatch(
								{},
								"400",
								LegendBox,
								{Legend Position( {5, [0, 1], 8, [2], 6, [-3, -3, 4, 5], 7, [-3], 9, [3]} ), Position(
									{0, 1, 2, -3, -3, 4, 5, -3, 3}
								)}
							)
						)
					)
				),
			Expr( __LineClgVlvCmd__ ), "Line[DonSteedES " || targetAhu || " ClgVlvCmd...]",
			Expr( __HeatmapClgVlvCmd__ ), "Heatmap[DonSteedES " || targetAhu || " ClgVlvCmd...]"
		)
	);
	
	// Save the current graphic, replacing the jpg name with the current target Ahu version
	optstartgraph << Save Picture( "C:\Projects\Projects\Hoke\Year 1\Jmp\Year 0\DonSteed\Feb16\" || targetAhu || ".jpg", "JPG" );
	optstartgraph << close window;
	// You can also close the current target data table if you want
	close( dt_Ahu, no save );
); // End of For Loop
Jim
himajafeb8
Community Trekker

Re: loop through column names which has same string and create subset

@txnelson Thank you so much . :) it's working!!

 

@uday_guntupalli Appreciate your help too! :)

0 Kudos