When I want to find all values of a field in table A that don't occur in table B I use the NP("difference"…) function. When this field is numeric and both tables have the same set of values I sometimes get {0} instead of the expected empty array. Using the NAV sytem table Integer:NP("join",NP("difference",NL("allUnique","integer","number","number","1|2"), NL("allUnique","integer","number","number","1|2")),"|")
yields "0" instead of ""
The results are correct whenever NP("difference"…) should evaluate to a non-empty array. Replacing the "allUnique" by "Filter" doesn't change the results.
What am I doing wrong here? (Excel 2007, Jet Reports 2010)
Date
Votes
4 comments
-
Jet Reports Historic Posts Official comment Hmmm, this is an interesting behavior. I think we may have made the NP array functions return 0 in cases where the array is empty for backwards compatibility with how it was being done in earlier versions of Jet (not necessarily because we want it that way but there may be people depending on it working that way). An easy workaround to this problem should be to wrap the NP(Difference) in an NL(AllUnique) like this:
NP("join",NL("AllUnique",NP("difference",NL("allUnique","integer","number","number","1|2"), NL("allUnique","integer","number","number","1|2"))),"|")
This is not very elegant I know but I believe it solves your problem. Does that work for you?
Regards,
Hughes -
Jet Reports Historic Posts Yes, this works, thanks a lot!
I now realise that the return value of NP("difference",{42},{42}) is 0 and not {0}, as I first thought.
Although (or because) I have programmed quite a bit I find Excel arrays (and Excel data typing in general) extremely puzzling. Do you happen to know a clear explanation of them (rather than just a bunch of examples) somewhere on the Web?
Thanks again,
Hans -
Jet Reports Historic Posts Hi Hans,
I don't have a link for you, but I can explain a little bit. Hopefully it helps.
Excel arrays are a somewhat puzzling subject. An array in general is just a list of values. In Excel, there are 1 dimensional arrays which is just a single list (like a column in Excel) or 2 dimensional arrays which are tables of values (like a sheet in Excel). Anytime you reference more than 1 value in an Excel worksheet, what you are referencing is an array. Certain Jet functions (NL(AllUnique), NP(Union), NP(Intersect), NP(Difference)) return arrays. When a function returns an array to a cell, all you see in the cell is the first value of the array. And even if a function returns an array, if you reference that function from another cell you just get the first value in the array, not the entire array. Thus you have to directly nest these Jet array functions inside other Jet functions in order for them to work right.
Excel has something called array formulas (the type where you have to do type CTRL+SHIFT+ENTER after typing the formula and after Excel evaluates it, it places {} around the formula). These formulas are designed to process Excel arrays in various ways. These are somewhat hard to understand, but the good news (or bad news) is that Jet is not compatible with Excel Array Formulas. If you run a report with them, they get messed up because there's no way for us to detect them and re-evaluate them as array formulas when we're copying values to and from very hidden sheets. So don't use those on your Jet reports. Does that help?
Regards,
Hughes -
Jet Reports Historic Posts Hi Hughes
Thanks for your explanation. It is the first I see that mentions some impossibilities (instead of just the cool things that one can do with them). One point in particular has had me banging my head against the wall for quite some time:[…] even if a function returns an array, if you reference that function from another cell you just get the first value in the array, not the entire array.
which, to me, looks a bit like Jet's problem with them:[…]because there's no way for us to detect [array formulas] and re-evaluate them as array formulas when we're copying values to and from very hidden sheets
I still don't quite understand why Excel can't have an ARRAY data type, (just like TEXT, DATE, TIME, etc). Of course the rules when copying such a value to a range (say A1:A10) would be complicated (what would be the type of say A5?)
But as I cannot (and don't need to) use array formulas in Jet reports anyway I am a happy camper again!
thanks
Hans
Please sign in to leave a comment.