Conditions quantity in $SELECT()
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?
Comments
Are you sure that you use it correctly, do you have a much closer example that will show that behaviour?
Ok, lets say there is table
SELECT
userActionone, userActiontwo, userActionthree, userActionfour, userActionfive, userActionsix, userActionseven, userActioneigth, userActionnine, userActionten, userActioneleven, userActiontwelve, userActionthirteen, userActionfourteen
FROM TEST.TESTThen 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
Still don't see any issues with $select, that look like Analyzer issue
What result should $select return if the conditions %source.userActionone=1 AND %source.userActiontwo=1 are true ?
Both. But if i add trird condition the result is still two
$SELECT(%source.userActionone=1:"First user action", %source.userActiontwo=1:"Second user action", 1:"Other")
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")
The result is the same: from one to four and with default.
I am not seeing the source data here, can you share that again?
Also, what does your build output show? You only have a count of 5, which means you either only have 5 rows in your table OR the rest are getting errors. If they are getting errors, we should be able to learn more about what is wrong with it
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 . . . .
Thanks for the comment. I tested $SELECT() in a similar script before writing this question and got the same result as yours.
Also take a look $CASE - in some cases can be less wordy and more effective.
I agree.
<FONT COLOR="#0000ff">$CASE</FONT><FONT COLOR="#000000">(1, </FONT><FONT COLOR="#800000">%source</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">userActionone</FONT><FONT COLOR="#000000">:</FONT><FONT COLOR="#008000">"User action one"</FONT><FONT COLOR="#000000">, </FONT><FONT COLOR="#800000">%source</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">userActiontwo</FONT><FONT COLOR="#000000">:</FONT><FONT COLOR="#008000">"User action two"</FONT><FONT COLOR="#000000">, </FONT><FONT COLOR="#800000">%source</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">userActionthree</FONT><FONT COLOR="#000000">:</FONT><FONT COLOR="#008000">"User action three"</FONT><FONT COLOR="#000000">, </FONT><FONT COLOR="#800000">%source</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">userActionfour</FONT><FONT COLOR="#000000">:</FONT><FONT COLOR="#008000">"User action four"</FONT><FONT COLOR="#000000">, </FONT><FONT COLOR="#800000">%source</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">userActionfive</FONT><FONT COLOR="#000000">:</FONT><FONT COLOR="#008000">"User action five"</FONT><FONT COLOR="#000000">, </FONT><FONT COLOR="#800000">%source</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">userActionsix</FONT><FONT COLOR="#000000">:</FONT><FONT COLOR="#008000">"User action six"</FONT><FONT COLOR="#000000">, </FONT><FONT COLOR="#800000">%source</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">userActionseven</FONT><FONT COLOR="#000000">:</FONT><FONT COLOR="#008000">"User action seven"</FONT><FONT COLOR="#000000">, </FONT><FONT COLOR="#800000">%source</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">userActioneigth</FONT><FONT COLOR="#000000">:</FONT><FONT COLOR="#008000">"User action eigth"</FONT><FONT COLOR="#000000">, </FONT><FONT COLOR="#800000">%source</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">userActionnine</FONT><FONT COLOR="#000000">:</FONT><FONT COLOR="#008000">"User action nine"</FONT><FONT COLOR="#000000">, </FONT><FONT COLOR="#800000">%source</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">userActionten</FONT><FONT COLOR="#000000">:</FONT><FONT COLOR="#008000">"User action ten"</FONT><FONT COLOR="#000000">, </FONT><FONT COLOR="#800000">%source</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">userActioneleven</FONT><FONT COLOR="#000000">:</FONT><FONT COLOR="#008000">"User action eleven"</FONT><FONT COLOR="#000000">, </FONT><FONT COLOR="#800000">%source</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">userActiontwelve</FONT><FONT COLOR="#000000">:</FONT><FONT COLOR="#008000">"User action twelve"</FONT><FONT COLOR="#000000">, </FONT><FONT COLOR="#800000">%source</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">userActionthirteen</FONT><FONT COLOR="#000000">:</FONT><FONT COLOR="#008000">"User action thirteen"</FONT><FONT COLOR="#000000">, </FONT><FONT COLOR="#800000">%source</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">userActionfourteen</FONT><FONT COLOR="#000000">:</FONT><FONT COLOR="#008000">"User action fourteen"</FONT><FONT COLOR="#000000">, :</FONT><FONT COLOR="#008000">"Other"</FONT><FONT COLOR="#000000">)</FONT>But the result will still be only one value, even if all %source.userActionXXX=1.
Thank for the comment. The result is the same
When constructing facts for a cube, each row of the source table is passed through the dimension logic and written to the fact table. In each row, some conditions are true, while others are false. Obviously, when rows pass through this logic, all 14 conditions are expected to be returned. But that does not happen for some reason.
Perhaps there are no rows that satisfy the dimension conditions?
Or maybe there are build constraints? (1000 rows?)
The source table I use in production always contains a matching row. If there's no user data, it's not recorded in the table. Therefore, the SELECT statement must match each condition with 100% certainty. And there are no restrictions (at least not ones that would exclude some conditions).
Ideally, if you share the code sample somewhere so devs can reproduce and see what's really going on
There's not much code. In fact, there's none at all. All I have is a table with some data (the data is confidential) and a $SELECT() with the conditions described. I reproduced my case almost identically. All happend inside Architect and Analyzer.
Still a test case would be helpful.
If there's no source table rows for which userActionten=1, that value will not show up as a dimension member in Analyzer (so no row in the pivot with COUNT equal to zero). Maybe that's what you're seeing?
Thank you for participation.
Take a look at this comment. I'm reposting a screenshot of the entire test table.
https://community.intersystems.com/post/conditions-quantity-select#comm…
The userActionten field in the last row has a value of 1. In fact, it is in all columns.
TEST.TEST.cls
Class TEST.TEST Extends %Persistent
{
Property userActionone As %Boolean [ SqlColumnNumber = 2 ];
Property userActiontwo As %Boolean [ SqlColumnNumber = 3 ];
Property userActionthree As %Boolean [ SqlColumnNumber = 4 ];
Property userActionfour As %Boolean [ SqlColumnNumber = 5 ];
Property userActionfive As %Boolean [ SqlColumnNumber = 6 ];
Property userActionsix As %Boolean [ SqlColumnNumber = 7 ];
Property userActionseven As %Boolean [ SqlColumnNumber = 8 ];
Property userActioneigth As %Boolean [ SqlColumnNumber = 9 ];
Property userActionnine As %Boolean [ SqlColumnNumber = 10 ];
Property userActionten As %Boolean [ SqlColumnNumber = 11 ];
Property userActioneleven As %Boolean [ SqlColumnNumber = 12 ];
Property userActiontwelve As %Boolean [ SqlColumnNumber = 13 ];
Property userActionthirteen As %Boolean [ SqlColumnNumber = 14 ];
Property userActionfourteen As %Boolean [ SqlColumnNumber = 15 ];
ClassMethod Fill()
{
&sql(truncate table TEST.TEST)
&sql(insert into TEST.TEST
(userActionone,
userActiontwo,
userActionthree,
userActionfour,
userActionfive,
userActionsix,
userActionseven,
userActioneigth,
userActionnine,
userActionten,
userActioneleven,
userActiontwelve,
userActionthirteen,
userActionfourteen)
select 0,0,0,0,0,0,0,0,0,0,0,0,0,0
union all
select 1,0,0,0,0,0,0,0,0,0,0,0,0,0
union all
select 0,1,0,0,0,0,0,0,0,0,0,0,0,0
union all
select 0,0,1,0,0,0,0,0,0,0,0,0,0,0
union all
select 0,0,0,1,0,0,0,0,0,0,0,0,0,0
union all
select 0,0,0,0,1,0,0,0,0,0,0,0,0,0
union all
select 0,0,0,0,0,1,0,0,0,0,0,0,0,0
union all
select 0,0,0,0,0,0,1,0,0,0,0,0,0,0
union all
select 0,0,0,0,0,0,0,1,0,0,0,0,0,0
union all
select 0,0,0,0,0,0,0,0,1,0,0,0,0,0
union all
select 0,0,0,0,0,0,0,0,0,1,0,0,0,0
union all
select 0,0,0,0,0,0,0,0,0,0,1,0,0,0
union all
select 0,0,0,0,0,0,0,0,0,0,0,1,0,0
union all
select 0,0,0,0,0,0,0,0,0,0,0,0,1,0
union all
select 0,0,0,0,0,0,0,0,0,0,0,0,0,1
)
}
}That's what I get:
PS: Have you tried rebuilding the cube?
Sorry for the delay. Your solution solved the problem. I was using the %Integer data type, but the solution was %Boolean.