0

I have two columns in a query.
I have 2 fields (Field 1 and Field 2). What I want to do is:

if Field 1 is blank then use Field 2, but

if it is not blank then I want it to populate it with Field 1.

I am sure it can be done in a computed column but have been unsuccessful with the logic (so far).

Community
  • 1
  • 1
Shelley
  • 11
  • 2

1 Answers1

0

"Blank" can mean a lot of things. You can try checking for the most common ones.

In a results set, this will check a few things.

    if( Field_1 == null || Field_1.replace(/\s/gi,'') == '' || Field_1 == NaN )
    {
      Field_2;
    }
    else
    {
      Field_1;
    }
  1. First is whether your field truly is blank, and contains no data. Hyperion results-set computed items like "null" to be all-lowercase.
  2. Second is whether you do have data, but it's invisible. Usually, that's empty strings, but it can also be other whitespace, so we'll see if replacing all whitespace (not changing the data; it's just for comparison-sake) with empty strings would yield an empty string. /\s/gi is regex ... it means "whitespace" global (ie: every match), case-insensitive.
  3. Lastly (and, I've never had this be the case personally, but I've been told it can happen), check to see if there's a Not-a-Number (NaN) response. This is sort of an error that can appear in data, counts as value, but which might not display on your screen.

If you're doing this in the Query section, you'll have to write it in SQL, and have to match the syntax to your data source (Oracle, DB2, Sybase, etc), but the concept is the same.

undrline - Reinstate Monica
  • 526
  • 1
  • 7
  • 18