topic Re: Need to create a column based on sequence of variable appear in another column in Discussions

Need to create a column based on sequence of variable appear in another column

HSS — Fri, 09 Jun 2023 15:59:22 GMT

Hi All,
I want to create a column based on :Client_ID and how many times ( a number of sequence is counted as 1 count only, see the the :Comment column) it is appearing in the data set. My requirement is :Final Requirement and would be happy to have :Required Column 1 as well. I can not re-arrange/sort the data.

Any help please ?

Many thanks, -HSS

Re: Need to create a column based on sequence of variable appear in another column

jthi — Thu, 29 Sep 2022 13:12:05 GMT

Using associative array to store the value and then increase as needed should work here.

Formula for Required Column 1:

As Constant(
	aa = Associative Array(:Client_ID);
	aa = Associative Array(aa << get keys, Repeat(0, N Items(aa)));
	retval = 1;
);
If(:Client_ID != Lag(:Client_ID),
	aa[:Client_ID]++;
);
aa[:Client_ID];

And Final Requirement:

Eval Insert("^:Client_ID^_^:Required Column 2^");

Example script (uses different column names):

Names Default To Here(1);

dt = Open("$DOWNLOADS/data.jmp");

dt << New Column("Required Column 2", Numeric, Ordinal, Formula(
	As Constant(
		aa = Associative Array(:Client_ID);
		aa = Associative Array(aa << get keys, Repeat(0, N Items(aa)));
		retval = 1;
	);
	If(:Client_ID != Lag(:Client_ID),
		aa[:Client_ID]++;
	);
	aa[:Client_ID];
));

dt << New Column("Final Requirement 2", Character, Ordinal, Formula(
	Eval Insert("^:Client_ID^_^:Required Column 2^");
));

Re: Need to create a column based on sequence of variable appear in another column

HSS — Thu, 29 Sep 2022 13:30:31 GMT

Hi @jthi, Can I do the grouping also on "Associated Array" using another columns ? Something like this ? "Associated Array(:Client_ID, :Region)" and my data set has more than 700000 rows, will the associate command still work ! If I can group by :Region, it may still have more than 10000 rows. In any case, I will certainly try this.
Thank you for your help. Regards, - HSS

Re: Need to create a column based on sequence of variable appear in another column

jthi — Thu, 29 Sep 2022 13:38:44 GMT

You would have to first create new "grouping" column if you wanted to use Associative Array like that. With multiple grouping columns, it might be easier to use Col Cumulative Sum (might be always easier, just came up with this). You will have to figure out correct comparison and the groupings

Col Cumulative Sum(:Client_ID != Lag(:Client_ID), :Client_ID)

Re: Need to create a column based on sequence of variable appear in another column

HSS — Thu, 29 Sep 2022 13:58:42 GMT

@jthi Col Cumulative Sum will work but I am not able to find a correct initialization of the loop/starting point with grouping variable :Region.
Any other suggestion?
Thanks

Col Cumulative Sum( :Client_ID != Lag( :Client_ID ) & :Region == Lag( :Region ), :Client_ID )

Re: Need to create a column based on sequence of variable appear in another column

jthi — Thu, 29 Sep 2022 14:11:52 GMT

Easiest thing is most likely to create new column and then use that as grouping instead of :Client_ID (original formula)

Col Cumulative Sum(:"Concatenate[Region,Client_ID]"n != Lag(:"Concatenate[Region,Client_ID]"n), :"Concatenate[Region,Client_ID]"n)

Otherwise the part which is summed in cumulative sum get more complicated quite fast (there might be easier ones than this, but this should handle wrong values on :Region change):

Col Cumulative Sum(
	If(Row() == Col Minimum(Row(), :Client_ID, :Region),
		1
	,
		:Client_ID != Lag(:Client_ID)
	)
	, :Client_ID, :Region
)

Re: Need to create a column based on sequence of variable appear in another column

HSS — Thu, 29 Sep 2022 14:28:00 GMT

@jthi Thank you for your help.
I will work along this direction.

Re: Need to create a column based on sequence of variable appear in another column

HSS — Fri, 30 Sep 2022 13:27:24 GMT

Hi All,

There is a further requirement - to add one more column :Req_3, where all the texts from :Group would be combined and stored. :Group text can be as long as 2 lines. Separator can be any thing.

The problem is - number of cells need to be combined is not fix, it depends upon the value in column :Req_1.

"Req_3" is required.
Any help, Thanks.

Re: Need to create a column based on sequence of variable appear in another column

jthi — Fri, 30 Sep 2022 15:13:03 GMT

Using SQL query (especially GROUP_CONCAT) or JMP's different table operations (split, transpose, text to columns and combine columns, join back) might be easier than formula (and faster), but you could do it with a formula for example by using Loc() and indexing of different columns.

Edit: Added interactive method

First split data:

Next transpose the split data:

From transposed data, first use text to columns to separate Req2 and Region from each other

Then use combine columns to combine Row columns

Rename and remove unnecessary columns and you should end up with something like this

Join this with the original data2

Remove extra columns and you should have what you need.

Enhanced Log might have script ready for you, but you might have to clean it up a bit