Question
· Nov 26

Conditions quantity in $SELECT()

#Analytics #Analyzer #InterSystems IRIS #InterSystems IRIS BI (DeepSee)

I use $SELECT() in Architect to aggregate various data conditions into a single dimension.

I have a data table like this: id, user, userData1, userData2...

Where each user action is the %Integer ID of that action from some source. Then in expression field i use: $SELECT(userAction1:"This user action", userAction2:"That user action"...) and so on, up to 14 conditions, excluding the default placeholder 1:"Other" (which is always empty because there is a condition with 100% chance). The logic is simple: if userAction1 is not null then SELECT return "This user action" and so on. The end result is that I get a perfect measurement with all the user data labeled the way I want. (Until the sum of the conditions hits an invisible wall)

After that, if we check the dimension in the analyzer, we find that only 10 conditions fall into this dimension; the rest are missing somewhere

I checked the documentation, and there's no description of the hard limit. I asked dc ai and he said the same thing. Perhaps the hard limit is an undocumented parameter?

Product version: IRIS 2025.3
Discussion (21)5
Log in or sign up to continue
Dmitrij Vladimirov · Nov 26

Ok, lets say there is table   

SELECT 
userActionone, userActiontwo, userActionthree, userActionfour, userActionfive, userActionsix, userActionseven, userActioneigth, userActionnine, userActionten, userActioneleven, userActiontwelve, userActionthirteen, userActionfourteen
FROM TEST.TEST

Then we randomly populate it with 0 and 1.

 

Then we create a cube, dimension and function for it
 

$SELECT(
%source.userActionone=1:"User action one", 
%source.userActiontwo=1:"User action two",
%source.userActionthree=1:"User action three",
%source.userActionfour=1:"User action four", 
%source.userActionfive=1:"User action five", 
%source.userActionsix=1:"User action six", 
%source.userActionseven=1:"User action seven", 
%source.userActioneigth=1:"User action eigth", 
%source.userActionnine=1:"User action nine", 
%source.userActionten=1:"User action ten", 
%source.userActioneleven=1:"User action eleven", 
%source.userActiontwelve=1:"User action twelve", 
%source.userActionthirteen=1:"User action thirteen",
%source.userActionfourteen=1:"User action fourteen", 1:"Other")


Compile and build. Then look in analyzer. Only four this time. My production tables return 10 no matter what

0 0
Vitaliy Serdtsev · Nov 27

Both.

Just for understanding. Will the result change if the conditions are reversed?
For example:

$SELECT(
%source.userActionfourteen=1:"User action fourteen",
%source.userActionthirteen=1:"User action thirteen",
%source.userActiontwelve=1:"User action twelve", 
%source.userActioneleven=1:"User action eleven", 
%source.userActionten=1:"User action ten", 
%source.userActionnine=1:"User action nine", 
%source.userActioneigth=1:"User action eigth", 
%source.userActionseven=1:"User action seven", 
%source.userActionsix=1:"User action six", 
%source.userActionfive=1:"User action five", 
%source.userActionfour=1:"User action four", 
%source.userActionthree=1:"User action three",
%source.userActiontwo=1:"User action two",
%source.userActionone=1:"User action one", 1:"Other")
0 0
Robert Cemper · Nov 26

I composed a small example as DV.MAC  with  36 conditions. and  -as expected- hit no limit.
 

DV ;
	for i=1:1:50 {
		set x=$r(40)
		set col=$SELECT(
 			x=1:"col_1",
			x=2:"col_2",
			x=3:"col_3",
			x=4:"col_4",
			x=5:"col_5",
			x=6:"col_6",
			x=7:"col_7",
			x=8:"col_8",
			x=9:"col_9",
			x=10:"col_10",
			x=11:"col_11",
			x=12:"col_12",
			x=13:"col_13",
			x=14:"col_14",
			x=15:"col_15",
			x=16:"col_16",
			x=17:"col_17",
			x=18:"col_18",
			x=19:"col_19",
			x=20:"col_20",
			x=21:"col_21",
			x=22:"col_22",
			x=23:"col_23",
			x=24:"col_24",
			x=25:"col_25",
			x=26:"col_26",
			x=27:"col_27",
			x=28:"col_28",
			x=29:"col_29",
			x=30:"col_30",
			x=31:"col_31",
			x=32:"col_32",
			x=33:"col_33",
			x=34:"col_34",
			x=35:"col_35",
			1:"more")
		write i,?4,x,?10,col,!
	}

result:

. . . . 
23  32    col_32
24  39    more
25  39    more
26  31    col_31
27  10    col_10
28  36    more
29  24    col_24
30  21    col_21
31  2     col_2
. . . .
0 0
Vitaliy Serdtsev · Nov 26

I agree.

$CASE(1,
%source.userActionone:"User action one", 
%source.userActiontwo:"User action two",
%source.userActionthree:"User action three",
%source.userActionfour:"User action four", 
%source.userActionfive:"User action five", 
%source.userActionsix:"User action six", 
%source.userActionseven:"User action seven", 
%source.userActioneigth:"User action eigth", 
%source.userActionnine:"User action nine", 
%source.userActionten:"User action ten", 
%source.userActioneleven:"User action eleven", 
%source.userActiontwelve:"User action twelve", 
%source.userActionthirteen:"User action thirteen",
%source.userActionfourteen:"User action fourteen", :"Other")

But the result will still be only one value, even if all %source.userActionXXX=1.

0 0
Vitaliy Serdtsev · Nov 27
 
TEST.TEST.cls

That's what I get:

   

PS: Have you tried rebuilding the cube?

1 0