Sign Up for Training |
Jet Express Support |
Jet Reports Company Site
Jet 365 Financials
Jet Professional
Jet Enterprise
Downloads
Community
Submit a Request
Give Feedback

Array Calculations


Arrays are lists of data values. You can obtain a string representing such a list from Jet using "Filter" as the What parameter in an NL function. The values in arrays returned by Jet are guaranteed to be unique. The resulting array might be a list of Customers or a list of Invoice Document numbers or any other list of data that match a set of filters. The array calculation operations of the NP function allow you to find different combinations of two arrays.

An example of when you would need an array calculation is listing the invoice document numbers where either the Type on an Invoice Line is "Item" for all item numbers, or the Type is "G/L Account" and the account number is 300. Both the Item numbers and the G/L Account numbers are stored in the same "No." field, so there is no single set of filters that will create this list of document numbers.

The array operations available in the NP function are "Difference", "Union" and "Intersect". The difference between two arrays consists of all of the elements that are in the first array but are not in the second. The union of two arrays consists of a single copy of all of the elements in both arrays with any duplicates eliminated. The intersection of two arrays is the set of elements that are common to both arrays. An example of the results of the array operations are listed in the table below.

Array 1
{100, 200, 300, 400, 500}
Array 2
{400, 500, 900, 1000, 2000}
Difference {100, 200, 300}
Union {100, 200, 300, 400, 500, 900, 1000, 2000}
Intersect {400, 500}

 

NESTING FUNCTIONS

In the examples below, the NL("Filter"), NP("Join"), NP("Union"), and NP("Split") functions are embedded within another 'parent' function [e.g., NL("Rows") ].  This is a requirement in older versions of Jet Essentials.  In Jet Essentials 2015 Update 1 and higher, as long as these functions do not include Excel cell references (e.g., B5), they can also reside on their own in separate cells and the 'parent' function can reference those cells.

 

Example 1

The following formula creates a list down rows of the union of all of the customers whose names start with A with all the customers whose names start with B.

Access Northwind

=NL("Rows", NP("Union", NL("Filter","Customers"," ContactName","ContactName","A*"), NL("Filter","Customers"," ContactName","ContactName","B*")))

=NL("Rows", NP("Union", NL("Filter","Customer","No.","Name","A*"), NL("Filter","Customer","No.","Name","B*")))

Great Plains Fabrikam

=NL("Rows",NP("Union",NL("Filter","RM00101","CUSTNMBR","CUSTNAME","A*"),NL("Filter","RM00101","CUSTNMBR","CUSTNAME","B*")))

Note that you could do the same operation with the following simple formula.

Access Northwind

=NL("Rows","Customers","ContactID","ContactName","A*|B*")

=NL("Rows","Customer","No.","Name","A*|B*")

Great Plains Fabrikam

=NL("Rows","RM00101","CUSTNMBR","CUSTNAME","A*|B*")

 

Example 2

Access Northwind

This formula creates a list down rows of the Order IDs of all of the orders where the Region field is either USA or Canada.

=NL("Rows", NP("Union", NL("Filter","Orders","OrderID","ShipCountry","USA"), NL("Filter","Orders","OrderID"," ShipCountry","Canada")))

=NL("Rows", NP("Union", NL("Filter","Sales Invoice Line","Document No.","Type","Item"), NL("Filter","Sales Invoice Line","Document No.","Type","G/L Account","No.","2000")))

Great Plains Fabrikam

The following formula creates a list down rows of the GL account 2nd segments where either the account type is 2 or the posting type is 0.

=NL("Rows",NP("Union",NL("Filter","GL00100","ACTNUMBR_2","ACCTTYPE",2), NL("Filter","GL00100","ACTNUMBR_2","PSTNGTYP",0)))

You should be cautious using arrays because they are often not the easiest or fastest way to solve a problem. Example 1 is a good example of a query that does not require arrays, and will run much slower if you use them. Also remember that, with Jet Essentials 2015 and earlier, if NP("Union"), NP("Intersect"), or NP("Difference") are by themselves in a cell they will only return the first value from the array. You must put them inside NL("Rows") as in the examples above in order to correctly return all the data.

There are two more array operations that behave a bit differently than those listed above: "Split" and "Join". "Split" takes two text strings and splits the first string based on the second, resulting in an array. For instance, if you wanted to create a list of account numbers based on the string "1000+2000+3000", the formula would look like the following.

=NP("Split","1000+2000+3000","+")

The result would be the array {"1000","2000","3000"}. Note that this must be put inside an NL("Rows") as in the Union examples above in order to return all the data.

In the opposite scenario, if you have an array but would like to create a text string by joining each element of that array separated by a given string, you would use the "Join" operation. Using the same array, you can create a string for a filter with array values separated by the "|" character with the following formula.

=NP("Join",{"1000","2000","3000"},"|")

The result would be the text string "1000|2000|3000", which is a valid filter that you could pass into an NL function.

For Join and Split, Arg1 of the NP function is the value you want to manipulate and Arg2 is the character by which you want to join or split the value. If you experiment with these operations, you will find that you have an amazing amount of flexibility, especially when you use them in conjunction with the other array calculation formulas listed above.

Please note that the results of an NP("Join") may be very large and thus putting it directly inside another function may cause problems with Excels 256 character formula limit as in the following formula.

=NL("Rows",NP("Split",NP("Join",{"some","array","here"},"|"),"|"))

It is recommended that in a situation like this the NP("Join") be placed in a separate cell as in the following.

B2: =NP("Join",{"some","array","here"},"|")

B3: =NL("Rows",NP("Split",B2,"|"),"|"))

 
Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request

Comments

  • Avatar
    j2associates

    Difference is not intuitive as you would expect it to be items not present in either array (e.g the opposite of the intersection). From a set perspective, you would expect difference + intersection = union. Instead, the Jet difference is only relative to the first array. Perhaps an Exclusive array operator could be provided to allow for exclusive values in all arrays.