I need to do a calculation which involves three columns and four constants. Calculate is a bit tedious for this so I am resorting to Javascript. I can program but am wholly unfamiliar with Javascript (or C).
The following is an extract, repeated correspondingly for other columns. It is based on the example code in /Help/Javascript
const aMul = 0.042;
var multiA = 0;
// (other constants and variables declared)
// Calc for non-empty cells
if ( !isNaN($(EPS Fcst 12M 3M Revis. (%))) ) {
multiA = aMul * $(EPS Fcst 12M 3M Revis. (%));
}
// (repeat for other columns and sum results to return)
I get the error message:
Cannot convert value ‘’ in column ‘EPS Fcst 12M 3M Revis. (%)’ row 2 to Number
for this and other columns where a cell is empty. I have also tried comparing with any and all of undefined, null and “” with appropriate logical operators.
Can someone enlighten me please?
Bonus query: In an earlier version with the same problem I had the column reference assigned to another variable for simplicity of later reference. Is that valid? e.g.
A sufficient sample of data for it to fail for me:
Code
Name
Share Price ($)
EPS Fcst 12M 3M Revis. (%)
Financial Health Rating
A2M
The a2 Milk Company Limited
8.530
12.11
1
AAI
Alcoa Corporation
53.900
0
ABB
Aussie Broadband Limited
3.920
8.38
1
I copied your code back except for fixing references for this data. After running, the added column is filled with “undefined”. ‘Type columns’ is set to default Number/Boolean/String.
I tried inserting a Replace prior to the Javascript, replacing empty with 0 in the relevant columns. It worked as expected and the Javascript then ran without error. That is probably a sound solution although the failure is still odd
Javascript has some very odd behaviour. I think the first version was created in a big hurry and now they can’t fix it, or it would break the Internet.
No rush. I have inserted a Replace transform before the javascript, which simplifies the latter greatly. Thanks to noticing the use of .toFixed() by Anonymous here I also save a subsequent Num Format so it breaks evenly.
Thank you Anonymous. Forgetting to define result probably comes from the fact JS is wholly new to me compared with some other scripting languages so I was not observant enough. When I created my current version using a prior Replace, I wrote that assignment starting as var result without noticing it was missing in the test case
The lack of “!” before isNaN is simply a typo when recreating this abbreviated example, the original program being correct there and the current version not needing it at all.
Ironically, having analysed the output of the working Transform, I have demonstrated that two things presumed to be related are not in any respect, leaving this as no further need. Still, that is an important outcome for me and EDT has proven again a useful data manipulation tool, and I am gaining familiarity with another of its features. All good.
I think my original form probably worked in the first place although I no longer have it to test again. The JS warnings I received were not fatal but being uncertain about script accuracy, I treated them as if they were. The new column was off-screen in column 23.
