Quite a few times already I have run into the following problem:
I make a report, using NP("Difference",…), to find all records with property A but not property B.
Everything works correctly as long as there are records with property A. If not (i.e. if array A is empty in the following formula) NP("Difference", A, B) returns B instead of an empty array.
So instead of, say, all unicorns without a car I get all animals with a car!
Example (where a, b, c and d have to be replaced by numbers:)
=NP("join",NP("difference",NL("allUnique","Integer","Number","Number",">a&<b"),
NL("allUnique","Integer","Number","Number",">c&<d")),",")
(a,b,c,d) = (1,10,4,7) correctly returns "2,3,4,7,8,9"
(a,b,c,d) = (4,7,1,10) returns "0" instead of "" (unexpected, but discussed here, with a simple workaround)
(a,b,c,d) = (7,4,1,10) returns "2,3,4,5,6,7,8,9" instead of "" (or even "0", like above)
Am I doing something wrong here? If not, is there a simple workaround?
Date
Votes
2 comments
-
Jet Reports Historic Posts Hi Hans,
Definitely a weirdness. However, there is an easy workaround. You should use NL(Filter) rather than NL(AllUnique). Change your formula to:=NP("join",NP("difference",NL("Filter","Integer","Number","Number",">a&<b"),NL("Filter","Integer","Number","Number",">c&<d")),",")
Then your 3rd example will return 0 just like your 2nd example. NL(Filter) is the recommended thing to use now rather than AllUnique. There are a few special cases where you must use AllUnique, but generally speaking, use Filter. Does that help?
Regards,
Hughes -
Jet Reports Historic Posts Does that help?
Yes it does!
Together with the workaround to avoid the "0" instead of "" whenever the result should be empty, the recipe becomes:=NP("join",NL("AllUnique", NP("difference",NL("Filter", <A…>), NL("Filter", <B…>))), ",")
Thanks a lot!
Hans
Please sign in to leave a comment.