when I use the getElementFromArray function I am getting inconsistent results. Just a simple refresh will change how the Array is structured and creates unwanted results.
image one: CRITS-array has 41019 in 0-index; formulation does not work as expected (CRITS2 field is blank, not showing the 41019).
image two: CRITS-array now has 41019 in 1-index; formulation works as expected (CRITS2 field populates with the 41019 element).
is there something I can do to prevent the array from restructuring?
building widget: Drop Schedule w/ Aisle Placement and Criticals
Hi Christina. In your widget, you are using buildArray(CRITS) two times in a row.
When applied the first time, it creates a list of unique CRITS values and the result will be an array of unique CRITS values.
When you apply the buildArray(CRITS) the 2nd time, it creates an array of unique CRITS arrays. So the result is an array of arrays. When you try to access an element from this last array with getElementFromArray, you will get back an array at the index position. If you are expecting to get the actual value, you need to apply this function twice.
Another important consideration is the usage of function buildArray: it does not guarantee ordering since it filters duplicate values. To ensure the proper order of the elements you need to use buildList function. It will preserve the order of elements before the group stage where the function is applied. However, note that buildList does not filter unique values. If you expect to see unique values only, you need to filter out duplicates prior to using this function.
Thank You for your insight! an array of array makes sense and that explains the troubles I was having with my work around. It looks like I’ve got something figured out to account for the changing order.
As for the ordering, it doesn’t matter what order the Array creates; once it establishes a pattern I can extract what I need, but why must the pattern change with every refresh of the page?
Imagine I have the following data that I would like to group by ORDER_NUMBER, calculate total ordered quantity, and also, get a list of unique order lines, as well as a list of all the order lines:
To do this, in a group stage, I would aggregate by ORDER_NUMBER, and for my aggregations, I would calculate the sum of all ORDER_QUANTITY fields, and then I would use buildArray function to create an array of unique LINE_NUMBERS in a field named LINES_UNIQUE_ARR, and then buildList function to create an array of all LINE_NUMBERS in the field ALL_LINES_ARR. Notice the order of the rows with respect to LINE_NUMBER before running the group stage.
Once I run the group stage here are the results:
As you can see the order of lines numbers in ALL_LINES_ARR follows the same order these lines were before executing the group stage. However, LINES_UNIQUE_ARR does not follow this order, as the the purpose of the function is to create a unique list of values, and the order of the fields is not respected.
If I run this widget again, I would get the following result where you can see the order of the lines in ALL_LINES_ARR is the same, but the LINES_UNIQUE_ARR is different:
This is all due to the function low-level function implementation at the DB level.
