Sort function breaks and shows err504 with a single row as a result

[roflas]

Hey, I am trying whole to day to figure this out, but I give up.

I have this function on my sheet:

SORT(FILTER(CHOOSE({1.2};$'Master log of records'.$B$5:$B$10033;$'Master log of records'.$A$5:$A$10033);$'Master log of records'.$B$5:$B$10033=B29;"NULL");2;-1)

It works fine, when FILTER results in at least two and more rows, but when that underlying FILTER table results in only 1 entry, it returns err504. I tested that with excel and it works fine there. It is really strange. It works normally if I simply have two rows, not one. Is there a reason for that? Is there a setup regarding this?

version 24.8.2.1

Thanks a lot.

[Regina]

Your formula is very complex. To narrow the problem down, please try a formula without the inner CHOOSE function.

Can you attach the document itself?

[roflas]

Hey, thanks for reply.

Actually this is just half of the formula. I took just the broken part. Formula is somewhat bigger.

I have attached the file. First sheet, first column, you can see variable results of this evaluation.
Idea is to display on first sheet, first column: document_number / last_number_used, i.e. like QF-004-001 / 002. That simply means that QF-004-001 is just a number of template and all derivatives are numbered with suffix like QF-004-001-001, QF-004-001-002 etc. Second sheet has all them (derivatives) listed. So I would like to have last no used on the first sheet next to the base document name. I do that by concatenating multiple strings of document number itself and then doing filtered (with swapped columns), sorted vlookup. This result is used with right function to get required symbols, i.e. end number of document.

test_file.ods (36.3 KB)

[Lupp]

Rather thorough investigation seems to show me that the behaviour of SORT() is basically due to a bug: The third argument isn’t handled as specified.
Strangely we get the error also with SORTBY() where the second argument should disambiguate the cases. Imo a bug.

[Lupp]

1. I gravely dislike too complicated formulas. They tend to cause lots of problems if little changes (maintenance) are needed or the cause of errors must be investigated.
2. On the other hand I also dislike sheets spoiled by lots of locked and possibly spilling ranges. And this isn’t just a personal dislike, but also a serious problem.
3. Thirdly the way formulas returning arrays are mostly used, the need to calculate intermediary results due to “1.” is in an unresolvable conflict with “2.”.

I therefore wrote a little package of user code (less than 100 lines of Basic - originally less than 50) which not only resolves the mentioned conflict, but also can be used as a bridge from the low-level functional programming by Calc formulas and the traditional imperative programming using variables. I named all the related routines starting with “bsm” for BackStageMemory.
This reaches back about 9 years, but the current thread is the first one to which I attach an example using the package.
If somebody wants to discuss my proposal, I will soon open a thread for the purpose in a classical forum…
See this example:
disask112713SortSortbyBugs.ods (33.4 KB)

[karolus]

@Lupp

I gravely dislike too complicated formulas. …

… Except they are mixed up with more complicated UDFs written by @Lupp ?!

Dont get me wrong, I would also avoid such Formulas as shown in this topic, but (IMHO) with your approach we are jumping out of the frying pan into the fire!

[Lupp]

@karolus

… Except they are mixed up with more complicated UDFs written by @Lupp ?!

I didn’t expect that exactly you would be interested in a factual discussion of my sketch.
However, I try to stress once more the difference between “long” and “complicated”.

1. The formulas I used aren’t quite short, but they are clear concerning their purpose.
2. The only functions from my package I used are bsmTake() and bsmGive() Their functionalty is clear and the code is clear and short.
3. The original problem is actually solved without the creation of locked ranges, and the intermediary result named “fi” helps essentially to calculate the needed condition for the workaround by giving the number of rows having passed the filter.

[koyotak]

@roflas

Es funktioniert einwandfrei, wenn FILTER mindestens zwei oder mehr Zeilen resultieren, aber wenn die zugrunde liegende FILTER Tabelle nur einen Eintrag ergibt, gibt es zurück err504 . Ich habe das mit Excel getestet und dort funktioniert es einwandfrei. Es ist wirklich seltsam. Es funktioniert normalerweise, wenn ich einfach zwei Zeilen habe, nicht eine. Gibt es dafür einen Grund?

@roflas

It works fine, when FILTER results in at least two and more rows, but when that underlying FILTER table results in only 1 entry, it returns err504. I tested that with excel and it works fine there. It is really strange. It works normally if I simply have two rows, not one. Is there a reason for that?

Simply explained, difficult to understand: every computer can only compare two values with each other, whether one value is greater, less or equal to a second value. Only one value leads to a logical error, because the comparison operator cannot compare with a missing second value, so cannot output FALSE or TRUE. It cannot process three values in one step; it must be instructed to do two subsequent steps. Using auxiliary functions in a function, faulty algorithms can be manipulated to produce an acceptable solution, which Microsoft understands masterfully, but the user is unaware of it.
An example: if a < b and b < c, then output the sorting: a, b, c; if both queries are FALSE, then sort: c, b, a. if a < /nothing/ than ERROR.

[roflas]

Sorry, but I do not get you. I do not see here some sort of multiple values you are talking about. As a scientist I do understand how numbers are compared and I do understand that sometimes things are beyond understanding of a user and how systems evaluate that. In this case, logic/sequence of that function is clear for me and result is what I expected when I built it some time ago step by step. It is not built just of luck.

I can live with it. It is not a problem and I do appreciate Libre for providing these tools.

Thank you.

[erAck]

You are attempting to sort on the second vector SORT(FILTER(...);2;-1) where FILTER for the “result if empty” delivers only a single scalar value "NULL", there is no second vector hence the error. You can workaround by replacing "NULL" with an array {"NULL";"NULL"}, note the ; semicolon here is the array row separator, adapt to your setting.

The difference to Excel probably (just a guess) is that Excel forces the “result if empty” argument to array and thus creates a 1x1 array and such if accessed with a larger offset is implicitly repeated to that dimension. Worth a bug report and then report the resulting bug number back here in the form tdf#123456.

[roflas]

Hey, thanks for response.

It seems to work with a single result, i.e. returns 001, when only one record is found, but for entries with higher values, result is incorrect. I.e. if I add {null; null} for line that should result in 13th number, it simply returns 002 or something like that.

I filed bug though.

[erAck]

@roflas please mention the bug number here as I indicated, thanks.

[Regina]

tdf#163592

[roflas]

Yes, I wanted to wait for confirmation:

Bug reported:

tdf#163592

[Lupp]

See new attachment to tdf#163592.

[karolus]

Hallo

=IFNA(IFERROR(INDEX(SORT(FILTER(Document_number;Document_name=B28;""); ;-1);1);INDEX(Document_number;MATCH(B28;Document_name;0)));"")

ask_112713.ods (24.5 KB)

or maybe:

=REGEX(IFNA(IFERROR(INDEX(SORT(FILTER(Document_number;Document_name=B28;""); ;-1);1);INDEX(Document_number;MATCH(B28;Document_name;0)));"");"-(\d{3})$";" / $1")

ask_112713_regex.ods (24.5 KB)

the last formula, but optimized:

=LET(n;
      FILTER(Document_number ;
             Document_name=B28;
             "");
      REGEX(
           IFERROR(
                   SORT(n ;1;-1) ;n);
           "-(\d{3})$";
           " / $1"))