For SuiteAnalytics Workbooks, is there any way in linked datasets to get null values to join? E.g. I'm linking two datasets on GL Account AND Location, but sometimes we have blank/null locations, however those financial lines are not pulling the custom record with that same GL account and blank/null value in the location field.

The issue probably comes from the fact that Null is not equal to anything, not even another Null, and since it would be checking is two Null values were equal, that fails according to the SQL standard. The only solution I can think of is to create a Location value for “No Location” or “N/A” and set all Null location records to have that instead, so that the matching would work as expected.

I take it you can't do matching on formula fields? i.e.

NVL({location},'none')

? *a formula on both datasets

Thanks to both of you! That's sort of what I figured, and I thought of the same thing for no location, but I don't think I'll be able to convince anyone to let me go back and update 6+ years of transaction data to make my mini-project work (nor do I really want to TBH). I will try the formula approach and see if I can make that work. That's a good thought. Will update here if I can get that functioning.

yeah, if the formula match is possible, that’s preferable to updating past records

@David B @scottvonduhn Took me a while to get back to this and to work through it, but I finally got it there. Turns out analytics DOES match on null/blank values, I was just mis-understanding how linked datasets and suiteanalytics workbook work. 😞 I missed that it like pre-aggregates everything into your union/link, so you can't disaggregate again in your pivot. Moral of the story, they really mean it when they say that everything in the rows area on a pivot MUST be a linked field. Led to me making some really weird joins on the linked datasets to get it working, but I got it there! Even though I was way off base on what the issue was, I definitely needed that extra bit of encouragement to keep me from giving up. Appreciate the help!

what a journey! hats off to your tenacity