868673_en-US

liliconcom

Database scripts

Hello everyone,

I'm trying to automate some changes in a data table so I can do some analysis.

I have the columns Ref, % and % UPDATED in my data table, and from a script I would like to build the columns A, B, ...., J:

Give column A:

- resume for line ref A (line 1): the data from the % UPDATED column

- resume for the other lines: the data from the % column

In my initial database, I have about 100 distinct rows in my Ref column, that's why I don't directly use an "If" formula to create my 100 new columns.

You will find the databases attached.

Thank you for your help.

Automation and ScriptingRe: Database scripts

You can for example split your table

, then fill in missing based on the % column and finally update the values to your original table. And there are many ways of doing this filling, one option is via JSL and data table subscripting

Names Default To Here(1);

dt = Datatable("BD");

dt_split = dt << Split(
	Split By(:Ref),
	Split(:"% UPDATED"n),
	Group(:Ref, :"%"n),
	Output Table("Split of BD by Ref"),
	Sort by Column Property,
	Invisible
);

cont_cols = dt_split << Get Column Names("String", "Continuous");
Remove From(cont_cols, 1); // drop %

perc_vals = dt_split[0, "%"];

For Each({cont_col}, cont_cols,
	perc_vals_current = perc_vals;
	non_missing = dt_split << Get Rows Where(!IsMissing(As Column(dt_split, cont_col)));
	perc_vals_current[non_missing] = dt_split[non_missing, cont_col];
	
	dt_split[0, cont_col] = perc_vals_current
);

dt << Update(
	With(dt_split),
	Match Columns(:Ref = :Ref),
	Replace Columns in Main Table(None)
);

Close(dt_split, no save);