topic Like COALESCE function in JSL in Discussions

Like COALESCE function in JSL

WakuWakuGoku — Sat, 10 Jun 2023 20:41:48 GMT

I have been looking for the function with which we could have an alternative value(e.g. A) if the column X is null, like COALESCE function, and the equation could be something like:

COALESCE(:X, A)

if X is not null, then it return the value of X

if X is null, then it return the value A

I understood that we manage to have such results by having a bit complicated equation, but I was wondering if we could have similar function in JSL by default.

Thank you very much for your help.

Re: Like COALESCE function in JSL

Jeff_Perkinson — Tue, 15 Dec 2020 12:01:00 GMT

There is no built-in COALESCE function in JSL. However, for a single column, the formula is not that complicated:

if( !Is Missing( :X ),
	:X, //:X is not missing
	"A" //else
);

Admittedly, it does get a bit more lengthy with more columns, but it's still not too bad.

if(
	!Is Missing( :X ), :X, //:X is not missing
	!Is Missing( :Y ), :Y, //:Y is not missing
	!Is Missing( :Z ), :Z, //:Z is not missing
	"A" //else
);

Is Missing() will take a list or matrix argument and return a list or matrix of 0s and 1s indicating missing or not in those locations.

Is Missing( {"a", " ", "c"} ); // returns {0, 1, 0}

Perhaps someone more clever than I can use that to make a simpler version.

And, of course, whatever method you choose can be used to create a custom function, Coalesce().

Re: Like COALESCE function in JSL

vince_faller — Tue, 15 Dec 2020 01:01:13 GMT

You could use loc nonmissing to just find the first one, it works for missing values as well as blank chars.

Names default to here( 1 );
dt = open("$SAMPLE_DATA\Big Class.jmp");
// blanking out some rows. 
dt:age[[2, 5, 8]] = .;
dt:sex[[2, 9, 18]] = "";
dt:height[[2, 4, 14]] = .;
dt:weight[[1, 4, 14]] = .;



coalesce = function({list_of_whatever}, 
	{DEFAULT LOCAL}, 
	l = Evallist(list_of_whatever);
	lnm = LocNonMissing(l);
	if(nitems(lnm), 
		return(char(l[lnm[1]])); // assuming you'll want to use both numeric and character columns
	);
);
// this should never be the weight value
New Column("Coalesce", character,
	<<Formula(coalesce({:age, :sex, :height, "will never get to weight", :weight}))
);
// if all values in the list are blank, it will just be ""
New Column("Coalesce all missing", 
	<<Formula(coalesce({:age, :sex, :height})) // row 2 should just be blank
);