This website uses Cookies. Click Accept to agree to our website's cookie use as described in our Privacy Policy. Click Preferences to customize your cookie settings.
Accept
Reject
Preferences
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Choose Language Hide Translation Bar
bjbreitling
bjbreitling
Level IV

Scripting group columns

Created: Jan 26, 2023 05:16 PM  |  Last Modified: Jun 8, 2023 12:40 PM (271 views)

Hi,

 

I have scripted an addin to query a sql table and then group/move some of the columns with an action below. It does group the columns but it also leaves them outside of the group post query. How do I not have them displayed outside of the group?

 

Open Database(
	"DSN=BB Analytics Data Warehouse;Description=BB Analytics Data Warehouse;UID=;Trusted_Connection=Yes;APP=JMP;WSID=SFX-5GCN2M1;DATABASE=BBAnalytics",
	"select * from dbo.NewCombinded",
	"NewCombinded"
);

 

 

// Move selected column: NextEndTime
Data Table( "NewCombinded" ) << Move Selected Columns( {:NextEndTime}, after( :NextStartTime ) );


// Group columns
Data Table( "NewCombinded" ) << Group Columns(
	"FermCount etc.",
	{:FermCount, :Volume, :BeerwellCount, :BWVol, :YP_Vol, :Latitude, :Longitude, :Design, :Tech}
);


// Rename column group: FermCount etc. → Plant Meta Data
Data Table( "NewCombinded" ) << Rename Column Group( "FermCount etc.", "Plant Meta Data" );


// Group columns
Data Table( "NewCombinded" ) << Group Columns(
	"Chlorine Total etc.",
	{:Chlorine Total, :Backset Solids, :Beerwell Drop pH, :Slurry %DS, :Flour Fines, :SlurrypH, :BacksetRate,
	:Level, :Temperature, :"Chlorine (gal)"n, :"Isostab (gal)"n, :Lipase Addition,
	:"PhibroXact 25/75 Addition"n, :"Sulfuric (gal)"n, :"Urea (gal)"n, :Lactoside247 Total, :Max Ferm Temp,
	:Phibro Total, :Sulfuric Acid Total}
);


// Rename column group: Chlorine Total etc. → Batch Ingredients
Data Table( "NewCombinded" ) << Rename Column Group( "Chlorine Total etc.", "Batch Ingredients" );


// Group columns
Data Table( "NewCombinded" ) << Group Columns(
	"Yeast Dead Cells etc.",
	{:Yeast Dead Cells, :Yeast Live Cells, :Yeast Total Cells, :"Yeast 1 Dose (gal)"n, :"Yeast 2 Dose (gal)"n,
	:"Yeast Chlorine (gal)"n, :Yeast Enzyme Amount, :"Yeast Urea (gal)"n, :Yeast Chlorine Totalizer,
	:Yeast Urea, :Yeast yTech Yeast Total, :Yeast Enzyme, :Yeast CIP Valve, :Yeast Level, :Yeast Temperature}
);


// Rename column group: Yeast Dead Cells etc. → Yeast Ingredients and Setpoints
Data Table( "NewCombinded" ) << Rename Column Group(
	"Yeast Dead Cells etc.", "Yeast Ingredients and Setpoints"
);


// Rename column group: Batch Ingredients → Batch Ingredients and Setpoints
Data Table( "NewCombinded" ) << Rename Column Group( "Batch Ingredients", "Batch Ingredients and Setpoints" );


// Move selected column: FermHrs
Data Table( "NewCombinded" ) << Move Selected Columns( {:FermHrs}, after( :FermHrs ) );


// Move selected column: Ferm Scrubber Water Ethanol
Data Table( "NewCombinded" ) << Move Selected Columns( {:Ferm Scrubber Water Ethanol}, after( :Glucose ) );


// Move selected column: Ferm Scrubber Water Ethanol
Data Table( "NewCombinded" ) << Move Selected Columns( {:Ferm Scrubber Water Ethanol}, after( :NextEndTime ) );


// Move selected columns
Data Table( "NewCombinded" ) << Move Selected Columns(
	{:Batch Ingredient #1, :Batch Ingredient #2, :Batch Ingredient #3, :Enzyme Amount, :Enzyme Amount 2,
	:Comments, :Batch Ingredient#1 Description, :Batch Ingredient#2 Description,
	:Batch Ingredient#3 Description, :Enzyme Type, :Enzyme Type 2, :Initials},
	after( :Acetic )
);


// Move selected columns
Data Table( "NewCombinded" ) << Move Selected Columns(
	{:Batch Ingredient #1, :Batch Ingredient #2, :Batch Ingredient #3, :Enzyme Amount, :Enzyme Amount 2,
	:Comments, :Batch Ingredient#1 Description, :Batch Ingredient#2 Description,
	:Batch Ingredient#3 Description, :Enzyme Type, :Enzyme Type 2, :Initials},
	after( :NextEndTime )
);


// Move selected columns
Data Table( "NewCombinded" ) << Move Selected Columns(
	{:"Yeast Dead Cells (Cells/mL)"n, :"Yeast Live Cells (Cells/mL)"n},
	after( :Ferm Drop Min pH )
);


// Move selected columns
Data Table( "NewCombinded" ) << Move Selected Columns(
	{:"Yeast Dead Cells (Cells/mL)"n, :"Yeast Live Cells (Cells/mL)"n},
	after( :Ferm Drop Final Density )
);


// Move selected columns
Data Table( "NewCombinded" ) << Move Selected Columns(
	{:"Yeast Dead Cells (Cells/mL)"n, :"Yeast Live Cells (Cells/mL)"n},
	after( :Ferm Drop Enzyme Total )
);


// Move selected columns
Data Table( "NewCombinded" ) << Move Selected Columns(
	{:"Yeast Dead Cells (Cells/mL)"n, :"Yeast Live Cells (Cells/mL)"n},
	after( :Ferm Drop Avg Temp )
);


// Move selected columns
Data Table( "NewCombinded" ) << Move Selected Columns(
	{:"Yeast Dead Cells (Cells/mL)"n, :"Yeast Live Cells (Cells/mL)"n},
	after( :NIR DDGS Storgage Fat )
);


// Move selected columns
Data Table( "NewCombinded" ) << Move Selected Columns(
	{:"Yeast Dead Cells (Cells/mL)"n, :"Yeast Live Cells (Cells/mL)"n},
	after( :Malt )
);


// Move selected columns
Data Table( "NewCombinded" ) << Move Selected Columns(
	{:Yeast Lot Number, :Yeast Lot Number #2, :Yeast Type},
	after( :Malt )
);


// Group columns
Data Table( "NewCombinded" ) << Group Columns(
	"Glucose etc.",
	{:Glucose, :Acetic, :Lactic, :Malt, :DDGS AFLATOXIN, :DDGS DON, :NIR DDGS Storage Protein,
	:NIR DDGS Storgage Fat, :NIR DDGS Storgage Gluc, :NIR DDGS Storgage Mst, :Ferm Drop Acetic,
	:"Ferm Drop Ethanol (Solids and Glyc Adj to Yield)"n, :Ferm Drop Avg Temp, :Ferm Drop Batch Id,
	:"Ferm Drop Batch ID (NIR)"n, :Ferm Drop DP3, :Ferm Drop DP4, :Ferm Drop DS, :Ferm Drop Enzyme Total,
	:Ferm Drop Ethanol, :"Ferm Drop Ethanol (NIR)"n, :"Ferm Drop Ethanol (Solids Adj)"n,
	:"Ferm Drop Ethanol (Solids and Glyc Adj)"n, :Ferm Drop Formic, :Ferm Drop Fruc, :Ferm Drop Gluc,
	:"Ferm Drop Gluc (NIR)"n, :Ferm Drop Glyc, :Ferm Drop Lactic, :Ferm Drop Malt, :Ferm Drop pH,
	:"Ferm Drop pH (NIR)"n, :Ferm Drop Starch, :Ferm Drop Total Sugars, :Ferm Starch Delta, :Ferm Starch In,
	:Ferm Starch Out, :Gallons Lost, :Significantly Different Batch}
);


// Rename column group: Glucose etc. → Drop End of Ferm Data
Data Table( "NewCombinded" ) << Rename Column Group( "Glucose etc.", "Drop End of Ferm Data" );


// Move selected columns
Data Table( "NewCombinded" ) << Move Selected Columns(
	{:FermCount, :Volume, :BeerwellCount, :BWVol, :YP_Vol, :Latitude, :Longitude, :Design, :Tech},
	after( :Sulfuric Acid Total )
);


// Move selected columns
Data Table( "NewCombinded" ) << Move Selected Columns(
	{:Glucose, :Acetic, :Lactic, :Malt, :DDGS AFLATOXIN, :DDGS DON, :NIR DDGS Storage Protein,
	:NIR DDGS Storgage Fat, :NIR DDGS Storgage Gluc, :NIR DDGS Storgage Mst, :Ferm Drop Acetic,
	:"Ferm Drop Ethanol (Solids and Glyc Adj to Yield)"n, :Ferm Drop Avg Temp, :Ferm Drop Batch Id,
	:"Ferm Drop Batch ID (NIR)"n, :Ferm Drop DP3, :Ferm Drop DP4, :Ferm Drop DS, :Ferm Drop Enzyme Total,
	:Ferm Drop Ethanol, :"Ferm Drop Ethanol (NIR)"n, :"Ferm Drop Ethanol (Solids Adj)"n,
	:"Ferm Drop Ethanol (Solids and Glyc Adj)"n, :Ferm Drop Formic, :Ferm Drop Fruc, :Ferm Drop Gluc,
	:"Ferm Drop Gluc (NIR)"n, :Ferm Drop Glyc, :Ferm Drop Lactic, :Ferm Drop Malt, :Ferm Drop pH,
	:"Ferm Drop pH (NIR)"n, :Ferm Drop Starch, :Ferm Drop Total Sugars, :Ferm Starch Delta, :Ferm Starch In,
	:Ferm Starch Out, :Gallons Lost, :Significantly Different Batch},
	after( :Tech )
);


// Move selected columns
Data Table( "NewCombinded" ) << Move Selected Columns(
	{:Yeast Lot Number, :Yeast Lot Number #2, :Yeast Type, :"Yeast Dead Cells (Cells/mL)"n,
	:"Yeast Live Cells (Cells/mL)"n, :Yeast Dead Cells, :Yeast Live Cells, :Yeast Total Cells,
	:"Yeast 1 Dose (gal)"n, :"Yeast 2 Dose (gal)"n, :"Yeast Chlorine (gal)"n, :Yeast Enzyme Amount,
	:"Yeast Urea (gal)"n, :Yeast Chlorine Totalizer, :Yeast Urea, :Yeast yTech Yeast Total, :Yeast Enzyme,
	:Yeast CIP Valve, :Yeast Level, :Yeast Temperature},
	after( :Significantly Different Batch )
);


// Move selected columns
Data Table( "NewCombinded" ) << Move Selected Columns(
	{:Ferm Drop Fill Density, :Ferm Drop Final Density, :"Ferm Drop Hours (NIR)"n, :Ferm Drop Hrs,
	:Ferm Drop Min pH},
	after( :Ferm Drop Ferm Number )
);


// Move selected columns
Data Table( "NewCombinded" ) << Move Selected Columns(
	{:Ferm Drop Ferm Number, :Ferm Drop Fill Density, :Ferm Drop Final Density, :"Ferm Drop Hours (NIR)"n,
	:Ferm Drop Hrs, :Ferm Drop Min pH},
	after( :NextEndTime )
);


// Move selected column: Total Backset To Ferm
Data Table( "NewCombinded" ) << Move Selected Columns( {:Total Backset To Ferm}, after( :NextEndTime ) );


// Move selected column: Ferm Drop W2M Ratio
Data Table( "NewCombinded" ) << Move Selected Columns( {:Ferm Drop W2M Ratio}, after( :NextEndTime ) );


// Move selected columns
Data Table( "NewCombinded" ) << Move Selected Columns(
	{:Month1, :Year1, :DateFermBatchStart, :Recipe Batch #},
	after( :NextStartTime )
);
0 Kudos
1 REPLY 1
wgardnerQS
wgardnerQS
Level II

Re: Scripting group columns

Feb 9, 2023 06:24 PM (210 views)  |  Posted in reply to message from bjbreitling 01-26-2023

You might check your use of Group Columns. Applying Group Columns will change the order of columns in the datatable to make the group members contiguous if they are not already contiguous. Also, a column can only appear in one group (I did not check your code for duplicates, just a note).

0 Kudos