Calculated Field

[ba11]

Hello All,

I have two field values with the following dimensions:

Blank - 

IF(SubStringCount([REQUIREMENT.DIM_EXPRESSION],'/')>0,
subfield([REQUIREMENT.DIM_EXPRESSION],'/',1),'1')

Weight -

IF(SubStringCount([REQUIREMENT.DIM_EXPRESSION],'/')=0,
[REQUIREMENT.DIM_EXPRESSION],'1')

I need the value of both fields multiplied, but when I do this, It only returns values for where substringcount>0 and returns null values for where it's = 0 as in the screenshot below.

The column where both Blank and Weight have been taken from is the one Field named 'Dimension'.

What am I doing wrong?

Thanks in anticipation.

[steeefan]

This is probably done best in the script. Define your fields there with the expressions you have, then multiply:

LOAD
  Blank * Weight AS BlankWeight,
  *;
LOAD
  IF(SubStringCount([REQUIREMENT.DIM_EXPRESSION],'/')>0, subfield([REQUIREMENT.DIM_EXPRESSION],'/',1),'1') AS Blank,
  IF(SubStringCount([REQUIREMENT.DIM_EXPRESSION],'/')=0, [REQUIREMENT.DIM_EXPRESSION],'1') AS Weight,
  //..
RESIDENT
  Table;

[ba11]

Thank you, I tried this and it still comes up with null for those fields.

[steeefan]

Right, I just saw it. You're using '1' as your default when when the if condition fails. Try setting that to just 1, i.e. a number, not a string.

Current:
IF(SubStringCount([REQUIREMENT.DIM_EXPRESSION], '/') = 0, [REQUIREMENT.DIM_EXPRESSION], '1')

New:
IF(SubStringCount([REQUIREMENT.DIM_EXPRESSION], '/') = 0, [REQUIREMENT.DIM_EXPRESSION], 1)

[ba11]

Thank you, but there are no changes still.

[steeefan]

For further support, either by me or someone else, please post the relevant part of the script and all the expressions and dimensions you are using in your table.

I quickly came up with this and I believe it to be working:

Data:
LOAD
  *,
  Blank*Weight AS BlankWeight;
LOAD
  REQUIREMENT.DIM_EXPRESSION,
  IF(SubStringCount([REQUIREMENT.DIM_EXPRESSION],'/') > 0,
    subfield([REQUIREMENT.DIM_EXPRESSION], '/', 1),
    1
  ) AS Blank,
  IF(SubStringCount([REQUIREMENT.DIM_EXPRESSION], '/')=0,
    [REQUIREMENT.DIM_EXPRESSION],
    1
  ) AS Weight;
LOAD
  If(REQUIREMENT.DIM_EXPRESSION = 'n', Null(), REQUIREMENT.DIM_EXPRESSION) AS REQUIREMENT.DIM_EXPRESSION;
LOAD * INLINE [
  REQUIREMENT.DIM_EXPRESSION
  1/1
  1/2
  1300
  2/2
  1
  0.5
  4/5
  2/3
  3/4
  n
];

[vinieme12 · Accepted solution]

It is returning Null values because you are multiplying with STRING of '1' rather than the Integer 1

Just remove the single quotes ; but you can simply this as below

=SubField([REQUIREMENT.DIM_EXPRESSION],'/',1)*coalesce(SubField([REQUIREMENT.DIM_EXPRESSION],'/',2),1)

Original 

= IF(SubStringCount([REQUIREMENT.DIM_EXPRESSION],'/')>0,
TRIM(subfield([REQUIREMENT.DIM_EXPRESSION],'/',1)),1)

* IF(SubStringCount([REQUIREMENT.DIM_EXPRESSION],'/')=0,TRIM([REQUIREMENT.DIM_EXPRESSION]),1)

[ba11]

Wow, that worked!

I was using 1 as an integer before but it seems the TRIM() function did the trick.

Thank you so much and thanks to everyone else that made a contribution.