Subscribe Bookmark RSS Feed

JSL: Differences in Column Reference Behavior

ABI

Community Trekker

Joined:

Jun 23, 2016

Goal: count how many rows of a column have empty values.

 

Method 1 (works):

dt = CurrentDataTable();
Print(NRows(dt<<GetRowsWhere(IsMissing(dt:ColName))));

 

 

 

Method 2 (works):

Print(NRows(dt<<GetRowsWhere(IsMissing(dt:Name("ColName")))));

 

Method 3 (doesn't work):

Print(NRows(dt<<GetRowsWhere(IsMissing(Column(dt, "ColName")))));

The error returned by debugger for Method 3 is "argument should be numeric{1} in access or evaluation of 'Is Missing' , Is Missing/*###*/(Column( dt, "ColName" ))"

 

So, it's pretty easy to understand that referencing a column by dt:ColName is identical to dt:Name("ColName"), but I'm confused as to why Column(dt, "ColName") performs differently.

 

In addition, if I were to do the following:

ref1 = dt:ColName;
ref2 = dt:Name("ColName");
ref3 = Column(dt, "ColName");

Then I get ref1 and ref2 holding a single numerical value (corresponding to the first value in the column), whereas ref3 is a reference to an appropriate Column object (as I would have expected all three declarations to accomplish).

 

 

The ref1 and ref2 results seems especially odd to me -- Method #1 and #2 above were returning the correct number of empty cells in the column, so obviously in those implementation of the reference it wasn't just looking at the first value in the column. It appears what is happening is that when dt:ColName is being set to a variable, it's unacceptable to use the entirety of dt:ColName and it's instead choosing to set the variable to dt:ColName[1].

 

Why have this behavior? When I use ref = dt:ColName, I would expect it to either return the same column reference generated by ref = Column(dt, "ColName") or perhaps a matrix/list full of values that correspond to the contents of the column in the order in which they appear.

 

Essentially, my questions boil down to (1) why do these behave differently, (2) is there an easy way to identify when dt:ColName needs to be used, and when Column(dt, "ColName") needs to be used, and (3) why does ref1 = dt:CFU return a reference to the first value in the column instead of either a reference to the column itself or a fully populated matrix/list that corresponds to all values in the column?

 

Thanks!

 

3 REPLIES
ABI

Community Trekker

Joined:

Jun 23, 2016

Update: just realized that Method #3 will work if using

 

Print(NRows(dt<<GetRowsWhere(IsMissing(Column(dt, "ColName")[]))));

 

Still feels like odd behavior to me. If ref = Column(dt, "ColName") returns a column reference, why do I need [] here?

 

I would expect the opposite -- since ref = dt:ColName only returns the first value in the column, I would expect to need to use [] in the GetRowsWhere(IsMissing(dt:ColName[])) statement, and since Column(dt, "ColName") already returns a whole column, why is [] necessary?

pmroz

Super User

Joined:

Jun 23, 2011

It will also work with AS COLUMN:

Print(NRows(dt<<GetRowsWhere(IsMissing(as Column(dt, "ColName")))));
Justin_Chilton

Joined:

Aug 27, 2015

The reason [] is needed is because Get Rows Where relies on the current row. Using Column("X")[] refers to the current row of column "X" and is equivalent to Column("X")[Row()].

As @pmroz said, AsColumn("X") will work because it returns a reference to the column at the current row.

Messages such as Get Rows Where and Select Where depend on the current row, in addition to For Each Row loops and column formulas.

Justin