date/column

[#1]

Hello everyone,

I have several columns:

1- “start date” columns

2- “End date” columns

Normally, each column belonging to group (2) should also belong to group (1) and vice versa. So we should have the same number of columns in both groups.

However, columns belonging to group 1 do not belong to group 2 and vice versa.

Example : "start date 9899" but the column "end date 9899" does not exist.

The objective is to have the intersection of these 2 groups so that there are the same columns in the 2 groups.

I am inspired by the following code:

Names Default To Here(1);

dt = Current Data Table();

// build 
aa_duree = Associative Array();
aa_nonduree = Associative Array();

For Each({col_name}, dt << Get Column Names("String"),
 If(Word(1, col_name) == "date début",
  aa_duree[Word(-1, col_name)] = col_name;
 , !IsMissing(Regex(col_name, "")),
  aa_nonduree[col_name] = 1;
 ,
  continue()
 );
);
show(aa_duree);
show(aa_nonduree);

aa_duree_copy = aa_duree;
aa_duree_copy << Intersect(aa_nonduree);
cols_to_keep = Insert(aa_duree_copy << get keys, aa_duree_copy << get values);

aa_duree_copy2 = aa_duree;
aa_nonduree_copy = aa_nonduree;
aa_duree_copy2 << remove(aa_nonduree);
aa_nonduree_copy << remove(aa_duree);
cols_to_remove = Insert(aa_nonduree_copy << get keys, aa_duree_copy2 << get values);

show(cols_to_keep, cols_to_remove);

[#2]

The example script you provided won't work because Word(1, col_name) will only return the first work of the string and you are comparing it to two words. So the if statement will always go to else-statement. There might be other issues, but that is at least one problem there. You can try changing Words for example to Starts With() https://www.jmp.com/support/help/en/17.0/#page/jmp/character-functions-2.shtml?os=win&source=applica.... Also make sure the cases are correct when comparing strings

[#3 · Accepted solution]

Here is my old school way to attack the issue

Names Default To Here( 1 );

dt = Current Data Table();

keep = {};
delete = {};

colNameList = dt << get column names( string );
For Each( {col, i}, colNameList, colNameList[i] = Uppercase( col ) );

While( N Items( colNameList ) > 0,
	If(
		Contains( colNameList[1], Uppercase( "Date début" ) ),
			found = Contains( colNameList, Substitute( colNameList[1], Uppercase( "début" ), "FIN" ) );
			If( found > 0,
				Insert Into( keep, colNameList[1] );
				Insert Into( keep, colNameList[found] );
				Remove From( colNameList, found, 1 );
				Remove From( colNameList, 1, 1 );
			,
				Insert Into( delete, colNameList[1] );
				Remove From( colNameList, 1, 1 )
			);,
		Contains( colNameList[1], Uppercase( "Date fin" ) ),
			found = Contains( colNameList, Substitute( colNameList[1], "FIN", Uppercase( "début" ) ) );
			If( found > 0,
				Insert Into( keep, colNameList[1] );
				Insert Into( keep, colNameList[found] );
				Remove From( colNameList, found, 1 );
				Remove From( colNameList, 1, 1 );
			,
				Insert Into( delete, colNameList[1] );
				Remove From( colNameList, 1, 1 )
			);,
		Remove From( colNameList, 1, 1 )
	)
);

show( keep, delete );

[#4]

Great, thanks for your help!