topic Re: Creating Groups after Database Query in Discussions

Creating Groups after Database Query

Hegedus1 — Fri, 18 Oct 2024 16:12:03 GMT

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

Re: Creating Groups after Database Query

jthi — Fri, 18 Oct 2024 16:36:20 GMT

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,...))

Re: Creating Groups after Database Query

Hegedus1 — Fri, 18 Oct 2024 17:51:40 GMT

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

Re: Creating Groups after Database Query

jthi — Fri, 18 Oct 2024 18:16:12 GMT

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);
);