Change rows in expression column

[davidwhiteman@mac.comParticipant]

I have created an expression column where all rows are bolded. I would like to make some changes to a few of the rows in this column. Can I clone this column into a non-bolded form to make the needed changes? How do I do that?

[davidwhiteman@mac.comParticipant]

Ohhhh. Figured it out. Just go to the expression column, select the drop-down gear menu and select ‘Convert to number column’.

[Jean-Yves Le StangParticipant]

Hi,

Be aware that the figures are bolded because they are part of an “Expression Column” (or a URL/File dtbin/dtable or Database link). Thus hey are the result of the calculation of an “Expression” (or a download link), which means that the values will change with the context of other parameters of this expression (or when refreshing the link).

Changing the column status from “Expression Column” to “Number Column” will break the relation and actual parameters will be replaced by their actual numerical value and will not be further automatically updated.

[davidwhiteman@mac.comParticipant]

Hi Jean-Yves,

 

Thanks so much for your comment. I can see now that the approach I am using is really not what I want. I can think of a new approach that I’ll try. The difficulty has to do with the fact that I have 3 years of time series data in the bolded column and want to accumulate values for each of the years individually starting on February 15. When I sum up the column it sums over all three years, but if I delete the value for February 14 of each year it sums the years individually as desired. I think I may now be able to create a couple of expression columns that subtract the accumulation of Feb 14 from the February 14 values, thus setting the initial value of the sum to zero. Anyway, I’ll give it a try. Thanks again for the advice.

[dgteamModerator]

You might want to have an intermediate mask column.

Here is an example of masking a column based on the date. Note the date has to be in the format yyyy:mm:dd, in the mask.

[Jean-Yves Le StangParticipant]

Hi David,

I can see better your issue now.

I guess you use the “.isum” function to cumulate the numbers in the value column. Indeed, the cumulated value is reset to zero each time there is a break in the series.

But I would suggest you to proceed differently in two steps

1. Elaborate a pivot command with the Date Column as Rows and the Value Column as Values, and map the Rows by Years. Selecting the “sum” calculation will compute the cumulated the values for each Year.
2. Then you have to filter the Dates so as to ignore dates preceding 15 Feb. I suggest to extract the Month and the Day from Date and elaborate as Expression Column : Day + 100Month. Therefore, the 15th Feb for each year, this criterion will be lower than 215. Then, use this criterion as mask, as shown in the screen capture below.

I hope this to be helpful

1. 

[davidwhiteman@mac.comParticipant]

Hi Jean-Yves and dgteam,

Thanks so much for your useful suggestions. I didn’t give you enough information to solve the problem completely. I actually wanted to do the running sum from Feb 15 to Feb 14 of the next year. I used the pivot command mapping the rows by days. I finally was able to do it in a roundabout way by going back to the original 15-min data and blanking out all the 15-min data values for all Feb 14s in the database. Then the running sums worked as desired – restarting when it encountered a blank daily value. I encountered lots of problems (typical when dealing with real observational data) and do have some suggestions for the developers that would have made this easier. It would be nice to be able to insert a blank value in the column of daily values produced by the pivot command. I could insert a NaN and any number that I pleased into the column, but I couldn’t find any way to insert a blank. I also tried the method suggested by dgTeam of simply marking the desired dates using an intermediate mask column. When you have a number of years, though, it would be helpful to extend the mask command using double conditions (e.g., date> 2022:02:15  && date ≤ 2023:02:15) rather than single conditions (e..g., =, <, >). And, it would be useful to have an ‘or’ option in addition to the && option.

Again, thanks so much for your comments and suggestions.

-Dave

[Author]

Posts