topic Re: How to select and repalce duplicates with blanks from one column based on duplicates in another column? in Discussions

How to select and repalce duplicates with blanks from one column based on duplicates in another column?

Neo — Tue, 03 Oct 2023 15:30:32 GMT

Lets say I have two column data. First column with English alphabets appearing sequentially in Capital case (A--> Z), with each alphabet appearing say 7 times before the next. The second column has the same repetition but in small case (a--z). I want keep only one entry for the small case letter in the second column for each Capital letter in column 1. Rest of the entries in second column I want to set as blanks.

(In my actual data the repetition is not fixed to 7 times and is variable. Also, my data is numerical and not character)

How to achieve this via JSL?

"Column 2 result" is what I want in "Column 2"

Re: How to select and repalce duplicates with blanks from one column based on duplicates in another column?

jthi — Tue, 03 Oct 2023 15:36:16 GMT

If your data is numeric how it goes from capital letters to non-capital ones? And is the repetition same in same data set or can it change between the values?

Re: How to select and repalce duplicates with blanks from one column based on duplicates in another column?

txnelson — Tue, 03 Oct 2023 15:57:05 GMT

If I am understanding what you want, I believe the solution is

names default to here(1);
dt=current data table();
dt << new column("result",
	formula(
		if(row()==1 | :column 1 != lag(:column 1), :column 2)
	)
);