New Topic
CommunityTopicQuestionReport Builder
Properties
Category Report Builder
Product Version 8.0

How to enter NULL in the calculated field formula builder?

Andrew Clauson shared this question 4 years ago
Answered

I'd like to create a calculated field that returns NULL if the WHEN condition is not met:

In SQL, it's a simple CASE WHEN [condition] THEN [do this if condition is met] ELSE NULL

However, in the calculated field formula builder (simple option), I can't seem to enter NULL, only the string 'NULL', which would be treated differently (and incorrectly).

I've tried building the calculated field using freehand SQL (referencing a field from a subquery), but that says the field referenced does not exist.

Any help would be much appreciated.

2 The same question

Replies (10)

photo
1
Jared Holtgrewe ● 4 years ago

Hi Andrew,

Thanks for reaching out with this question.

When returning null values the freehand builder should be used. I was able to reference a field within a sub-query with this calculation :

/403e6d6fab4fc453629a41ea203a9f71

This returns the 'Nice' and the null.

I was running into issues referencing the field until I included the table name the field is in within the reference like so: 

`tableName`.`fieldName`

Please let me know if this works for you as well.

Thanks,

Jared

Reply
photo
1
Andrew Clauson ● 4 years ago

Thanks, Jared. I’ll try that out.

From: Support Queue <support@Yellowfin.bi>

Reply-To: Support Queue <support@Yellowfin.bi>

Date: Tuesday, February 25, 2020 at 2:33 PM

To: Andrew Clauson <andrew@profinancial.bc.ca>

Subject: New Comment in "How to enter NULL in the calculated field formula builder?"

Reply
photo
1
Jared Holtgrewe ● 4 years ago

Hi Andrey,

I wanted to reach out to see how This went.

Were you able to resolve the issue with my suggestion?

Regards,

Jared

Reply
photo
1
Andrew Clauson ● 4 years ago

Hi Jared,

Thanks for following up. We’re having some database challenges on our end so I haven’t been able to try it yet. Hopefully in the next day or two.

Andrew

From: Support Queue <support@Yellowfin.bi>

Reply-To: Support Queue <support@Yellowfin.bi>

Date: Thursday, February 27, 2020 at 12:36 PM

To: Andrew Clauson <andrew@profinancial.bc.ca>

Subject: New Comment in "How to enter NULL in the calculated field formula builder?"

Reply
photo
1
Jared Holtgrewe ● 4 years ago

Hi Andrew,

Thanks for the update.

I will reach out next will reach out next week to see where we're at.

If you are able to test before then, please don't hesitate to reach out.

Regards,

Jared

Reply
photo
1
Jared Holtgrewe ● 4 years ago

Hi Andrew,

Hope things are going well.

Have you been able to test my suggestion?

Please let me know the results.

Thanks,

Jared

Reply
photo
1
Andrew Clauson ● 4 years ago

Hi Jared,

Sorry, we’re still dealing with some data problems on our end and I haven’t had a chance to test. Feel free to close this ticket in the meantime and I’ll test out your solution as soon as I can. Thanks for following up.

Andrew

From: Support Queue <support@Yellowfin.bi>

Reply-To: Support Queue <support@Yellowfin.bi>

Date: Friday, March 6, 2020 at 9:21 AM

To: Andrew Clauson <andrew@profinancial.bc.ca>

Subject: New Comment in "How to enter NULL in the calculated field formula builder?"

Reply
photo
1
Jared Holtgrewe ● 4 years ago

Hi Andrew,

Thanks for the update.

I completely understand more important things coming up. I'll go ahead and mark this as answered for now.

Once you are able to test, feel free to reply here and the ticket will be re-opened.

Thanks,

Jared

Reply
photo
1
Andrew Clauson ● 4 years ago

Hi Jared,

Just wanted to give you an update here and close this ticket once and for all, as my issue has been resolved.

As the chart I was trying to build is ultimately a Javascript chart, Nathan on your consulting team helped me adjust the JS code so I didn’t end up needing to implement this NULL logic after all.

Thanks again for your help, though. Much appreciated.

Andrew

From: Support Queue <support@Yellowfin.bi>

Reply-To: Support Queue <support@Yellowfin.bi>

Date: Friday, February 28, 2020 at 8:57 AM

To: Andrew Clauson <andrew@profinancial.bc.ca>

Subject: New Comment in "How to enter NULL in the calculated field formula builder?"

Reply
photo
1
Jared Holtgrewe ● 4 years ago

Hi Andrew,

Thank you for updating me on this.

Glad to hear you were able to get help through Nathan to get this resolved. I'll go ahead and mark this as Answered.

Please feel free to reach out with any other issues or questions.

Thanks,

Jared

Reply
Leave a Comment
 
Attach a file