How to Set Duplicate Content in a Dataset to Null with esProc

The first two fields of a certain database table may have duplicate values, such as the first three records below.

Now we need to change all duplicate values to null. In other words, after grouping by the first two fields (equivalent to grouping by one of the fields), only the first record in the group remains unchanged, and the first two fields of other records are changed to null.

The calculation result is as follows:

After SQL grouping, it must aggregate immediately, and the grouped subsets cannot be kept for further calculation. SQL also does not have natural row numbers within the group, making the code difficult to write.

esProc provides a rich set of calculation functions that can keep grouped subsets for further calculation, with natural row numbers, including row numbers within the group 👉🏻: TryDEMO.

A1： Load data.

A2： Group by the first field using the group function, but do not aggregate.

Each group is a set that can be expanded by clicking, as shown in the first two groups.

A3： Use the run function to modify the data of each group. When the member’s index in the group is greater than 1, change the first two fields to null. ~ represents the current group, and # represents the sequence number within the group.

A4： Merge the groups.

The step-by-step code above is beneficial for learning and debugging. Once proficient, A2-A4 can be combined into one statement:

=A1.group(Column_A).run(~.(if(#!=1,Column_A=Column_B=null))).conj()

esProc SPL FREE Download – Free Trial Available, Download Now!