Creating Groups after Database Query

Question

Hi,

I have a database query that returns a large table, 200+ columns,  and would like to clean it up before working with.

The columns have initial terms that signify the metrology tool source such as Raman, XRD, XRF, BET, PSA,...

For each of these terms I would like to create a group and use the name from the above list to act as the name of the group.

for example there are 20 columns that start with the term Raman and I would like them all grouped together.

How should I script it?  I tried to create a list using Name starts with("Raman") but that seems to return an empty list.

Side note: I asked ChatGPT but that was useless.

Andy

jthi · Answer

Few questions:

* Do you wish to group all columns?
* Do your columns have clear separator for the group such as underscore (Raman_)

Names Default To Here(1);

dt = Current Data Table();

CL = dt << Get Column Names();
Raman = {};
For Each({value}, CL,
	If(Starts With(value, "Raman"), 
		Insert Into(Raman, value);
	);
);

dt << Group Columns("Raman", Raman);

And full example based on the examples I have

Names Default To Here(1);

dt = New Table("Untitled 2",
	Add Rows(1),
	Compress File When Saved(1),
	New Column("Raman1", Numeric, "Continuous", Format("Best", 12), Set Values([.])),
	New Column("Raman2", Numeric, "Continuous", Format("Best", 12), Set Values([.])),
	New Column("XRD1", Numeric, "Continuous", Format("Best", 12), Set Values([.])),
	New Column("Raman3", Numeric, "Continuous", Format("Best", 12), Set Values([.])),
	New Column("XRD2", Numeric, "Continuous", Format("Best", 12), Set Values([.]))
);

collist = dt << Get Column Names("String");
aa_groups = Associative Array();
aa_groups["Raman"] = {};
aa_groups["XRD"] = {};

For Each({groupname}, aa_groups << get keys,
	For Each({value}, collist,
		If(Starts With(value, groupname), 
			Insert Into(aa_groups[groupname], value);
		);
	);	
);

For Each({{groupname, cols}}, aa_groups,
	dt << Group Columns(groupname, cols);
);

Hegedus1 · Answer

This is where I am starting and no this does not work.

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

CL = dt << Get Column Names();
Raman={};
For Each ( {value,index},CL,if(starts with(value,"Raman"),Raman<<insert item(value),));
print(raman);

Next steps

Andy

jthi · Answer

This partially depends on your data but the general idea usually is

* Get list of your columns
* loop over the list while collecting the columns into appropriate groups
* loop over the appropriate groups and create the column groups

For Each() for looping, Associative Array() for data structure and possibly Starts With() for checking for groups (or Word(1,...))