Table Format for Sort by date?

Everything related to our flagship word processor.
adryan
Posts: 561
Joined: 2014-02-08 12:57:03
Location: Australia

Re: Table Format for Sort by date?

Post by adryan »

G’day, all

And just in case there are any Australians out there who use the format “21 December, 2019” or the ordinal equivalent, here is another set of macros:–

Date AU → ISO.nwm
(22.15 KiB) Downloaded 655 times

Date ISO → AU.nwm
(22.81 KiB) Downloaded 668 times

Date ISO → AU Ordinal.nwm
(23.11 KiB) Downloaded 646 times

Cheers,
Adrian
MacBook Pro (M1 Pro, 2021)
macOS Ventura
Nisus Writer user since 1996
withoutFeathers
Posts: 140
Joined: 2013-03-19 16:22:50

Re: Table Format for Sort by date?

Post by withoutFeathers »

Thank you both!

I'm amazed and a little overwhelmed at the moment, by the amount of testing I'll need to do to see what works best (or, dare I say it, what works, since I've already found several small anomalies in testing them).

But I'm going to forge ahead, and try to keep your macros straight by changing the names to add "Dr2" to ones that have been superceded, so I can report something trackable in case you wish to repair the anomalies. :)

I'll do most of this testing later, probably tomorrow, but just report now that the one that's interesting me most at the moment is the one that sorts the table in one go, which will be trickiest I expect. (ie. "Sort Table Rows By Selected Date Column".)

On my first tests it threw an error, and I'll just ask if it's capable of handling a table with a heading row of column titles? Not sure this is the problem yet though.

wF
adryan
Posts: 561
Joined: 2014-02-08 12:57:03
Location: Australia

Re: Table Format for Sort by date?

Post by adryan »

G'day, wF et al

How about this one? As long as the date comes first, it will operate on a selection to sort ordinary paragraphs, list items and tables. The day of the month is allowed to be expressed as an ordinal.

Sort by Date US.nwm
(24.41 KiB) Downloaded 623 times

Cheers,
Adrian
MacBook Pro (M1 Pro, 2021)
macOS Ventura
Nisus Writer user since 1996
User avatar
phspaelti
Posts: 1313
Joined: 2007-02-07 00:58:12
Location: Japan

Re: Table Format for Sort by date?

Post by phspaelti »

withoutFeathers wrote: 2019-08-31 16:11:12…but just report now that the one that's interesting me most at the moment is the one that sorts the table in one go, which will be trickiest I expect. (ie. "Sort Table Rows By Selected Date Column".)

On my first tests it threw an error, and I'll just ask if it's capable of handling a table with a heading row of column titles? Not sure this is the problem yet though.
Sorry about that. That should be fixed. Also I have now added—per your suggestion—a feature that should allow for a header row.
Attachments
Sort Table Rows By Selected Date Column.nwm
(7.25 KiB) Downloaded 425 times
philip
withoutFeathers
Posts: 140
Joined: 2013-03-19 16:22:50

Re: Table Format for Sort by date?

Post by withoutFeathers »

Here is my test report. It's complicated but was certainly worthwhile doing.
Thank you Philip and Adrian for those macros. Here goes:

I used the revised versions of three of the macros that you revised, and called them "Dr2" in my naming.

I ended up with 6 distinct macros to test, which I prefixed 1-6; specifically the resulting macro file names were:
  • 1- Date To ISO (Philip)
    2- Date US → ISO-Dr2 (Adrian)
    3- Date ISO → US-Dr2 (Adrian)
    4- Date ISO → US ordinal (Adrian)
    5- Sort Table Rows By Selected Date Column-Dr2 (Philip)
    6- Sort by Date US (Adrian)
So I had three types of transformations being tested:
1,2 are Date to ISO (one each Philip and Adrian)
3,4 are ISO to Date (both Adrian)
5,6 are Sort column by date. (one each Philip and Adrian)

How I Tested:
I used a single Test table with about 30 records. I pasted in a fresh copy for each of the 6 macros to work on.
This test table was pre-existing and has various challenges, including:
-- some cells have only a year date like '2003'
-- there are blank rows (and so blank cells in the Date column)
-- dates are not all US standard format (lots of variety, like 'Aug 6 / 19', etc.)
-- there's a top row of titles of the columns that shouldn't sort

Later I'll paste in my full test notes for each, but here's the summary:

Conclusions:
#1, Works, except for blank cells (which it creates date for) and short dates (‘2003’) which it creates day and month for. Needs minor debugging.
#2, Mostly doesn’t work, since I’m not using US pure date formats.
#3. Works all.
#4. Works except errors in some abbreviations (July 12nd, 1977, etc.). Needs minor debugging.
#5. Works all. Feature request: choice of ascending or descending.
#6. Doesn’t work directly on multi-format dates. On US dates, changes to ISO and then gets stuck without sorting. Can be sorted afterwards. Needs some debugging.


My fuller test sequence for each is below.

Test Date Macro #1:
1. Select “Date Submitted” column cells, excluding title row, but including blank cells.
2. Choose Macro #1.
Result:
All correct except anomalies:
— Blank cell changes to “2000-01-01” instead of staying blank.
— Truncated date with only year (ie., ‘2003’) changes to correct year but with addition of today’s date (i.e. becomes ‘2003-09-01’) instead of just staying ‘2003’.

Test Date Macro #2:
1. Select “Date Submitted” column cells, excluding title row, but including blank cells.
2. Choose Macro #2.
Result:
Almost all don’t work out of 20 different rows in table. Only pure US dates were changed, ie, ‘July 12, 1977’ worked; but all others with truncations or slashes etc. were ignored or partially changed incorrectly. Unfortunately I’m likely to vary in my date formats greatly and so this is not usable in this circumstance.

Test Date Macro #3
Method:
Create ISO by using Date #1 Macro on the “Date Submitted” column, including blank cells.
— Then attempt to reverse this to US date format with Date #3
Result:
Perfect reversal of all ISO to U.S. date formats, including skipping blank cells and leaving truncated (‘1986’) dates as they are.

Test Date Macro #4
Method:
Create ISO by using Date #1 Macro on the “Date Submitted” column, including blank cells.
— Then attempt to reverse this to US ordinal date format with Date #4
Result:
— Numbers are correct, but several ordinal abbreviations are rendered incorrectly:
July 12nd, 1977
April 12nd, 2018
October 12nd, 2018
December 13rd, 2018
September 12nd, 2018

Test Date Macro #5. Sort Entire table by date column.
Method: Choose column and apply Macro.
Result: Worked!
—one anomaly: empty rows are sorted to between the ‘1986’ date and the ‘2003’ date. This is probably because (see anomaly in Test #1) blank cells are being read as if they are 2000-01-01.
One request: it would be nice to have ‘ascending’ and ‘descending’ choice box, like in regular table sorts.

Test Date Macro #6. Sort Entire table by date column.
Method: Choose column and apply Macro.
Result: Macro gives error in ‘line 6’. Cancelled.
Revise: This Macro is for U.S. dates, so fair thing to do is transform the dates to U.S. date first, by using sequence Date Macro #1 (to ISO) -> Date Macro #3 (to US from ISO) -> Date Macro #6 (sort column).
Result: First two steps done OK (blank row blanked again, correcting anomaly in Macro #1), then Macro 6:
— error in line 28
Try by selecting all relevant rows, rather than just cursor insertion in column:
— changed all the dates to ISO, AND then gave error.
— However I successfully sorted now on ISO date (using other Macro, regular table sorting)
However this is a longer way around than doing it manually with the other macros, so needs work.

That's all for now!

I need to go for a walk...
wf
User avatar
phspaelti
Posts: 1313
Joined: 2007-02-07 00:58:12
Location: Japan

Re: Table Format for Sort by date?

Post by phspaelti »

Hello again feathers,
Nice job with all that testing!

Here is an updated version with the ascending/descending feature added:
Sort Table Rows By Selected Date Column.nwm
(7.38 KiB) Downloaded 443 times
As to the other points:
I'm not sure what's going on with blank cells for you. For me blank cells sort at the end, or at the beginning when sorting descending, which is what I expect. With macro 1, blank cells just give me an error (which is also what I expect). Are you sure the cells are blank? If you wanted more debugging done on that you would have to provide me with some kind of sample file.

Now for the year only case: This is to be expected, sort of. Actually I would have expected Nisus to come up with 2003-01-01, not 2003-[today's date]. But this is not something I'm going to worry about. It's just an artifact of using the Date.newWithText function. I'm using that because I don't want to fuzz around with dates, and all the margin cases. If you want to know more about how that works, here is some code that you can use to test with various inputs:

Code: Select all

$date = ''
while @true
$sel = prompt input $date
$date = Date.newWithText($sel)
end


One could, of course, write some code to deal with such cases in a way that better suits your needs, but at this point I refer you back to what I wrote about macro-writing earlier.
philip
adryan
Posts: 561
Joined: 2014-02-08 12:57:03
Location: Australia

Re: Table Format for Sort by date?

Post by adryan »

G'day, wF, Philip et al

Full marks for the heroic testing effort, wF! I much appreciate your going to the trouble of testing all the solutions Philip and I have offered.

Thanks for pointing out the erroneous ordinal expressions in my macros. How silly of me! Sorry about that. I think I’ve now rectified them in these revised versions. I have also corrected a bug in the sorting macro that may have ordinalized dates unnecessarily when an ordinal suffix appeared elsewhere in the paragraph.

Date ISO → US Ordinal.nwm
(23.41 KiB) Downloaded 469 times

Date ISO → AU Ordinal.nwm
(23.41 KiB) Downloaded 438 times

Sort by Date US.nwm
(24.78 KiB) Downloaded 441 times

The most recently uploaded versions of the following macros should still be OK:–

Date US → ISO.nwm
Date AU → ISO.nwm
Date ISO → US.nwm
Date ISO → AU.nwm

I haven’t yet done the macro for sorting Australian-formatted dates.

When it comes to the multiple date formats wF is contending with, this is really something that needed to be set out explicitly before going to the trouble of testing macros that were never designed to deal with them. Ideally one would have been provided at the outset with a sample table and an exhaustive list of possible date formats to be encountered.

Now that we have some intimation of the extent of wF’s predicament, the question is, how best to proceed?

I consider ordinal dates to be sufficiently common to accommodate them in the macros I have offered. But, for various reasons, I am less enthused about accommodating other date variants in them.

Instead, I would suggest a preliminary, separate, “data cleaning” operation before the sorting operation. The idea would be to reduce the number of formats to as few as possible. All years, for example, would be 4-digit numbers. All months would be designated consistently, whether by full name or by the initial three letters of the name. If a solidus is used anywhere, one needs to settle on a consistent interpretation. And so on. Whether one does the data cleaning by hand or with the aid of a macro is a matter of convenience.

The subsequently applied sorting macro would need to accommodate the data it is supplied with as input. In general, though, I would recommend the data cleaning procedure be separate from the sorting one, as the latter is likely to be more generally useful in other contexts.

You would need to decide on the appearance of the sorted dates in the final output. Do you want them standardized (in line with the output of your cleaning operation) or do they need to appear exactly as they did originally? If the latter, Philip’s technique of using a temporary working column would make life easier, at least for Table situations.

Cheers,
Adrian
MacBook Pro (M1 Pro, 2021)
macOS Ventura
Nisus Writer user since 1996
adryan
Posts: 561
Joined: 2014-02-08 12:57:03
Location: Australia

Re: Table Format for Sort by date?

Post by adryan »

G’day, all

My aim all along in this project has been to devise a sorting routine that would work in a variety of situations, not just in Tables. In these broader contexts, things that look a bit like dates or parts thereof may not necessarily be the appropriate sort targets. I think I’ve now addressed the various issues, including Australian-formatted dates, and so offer the following versions of my macros.

Sort by Date US.nwm
(26.27 KiB) Downloaded 452 times

Sort by Date AU.nwm
(26.27 KiB) Downloaded 441 times

Date US → ISO.nwm
(23.2 KiB) Downloaded 464 times

For some reason, I could not upload the remaining macros in the set, so I’ll try attaching them to another posting.

Cheers,
Adrian
MacBook Pro (M1 Pro, 2021)
macOS Ventura
Nisus Writer user since 1996
adryan
Posts: 561
Joined: 2014-02-08 12:57:03
Location: Australia

Re: Table Format for Sort by date?

Post by adryan »

G’day, all

Date ISO → US.nwm
(22.78 KiB) Downloaded 596 times

Date ISO → US Ordinal.nwm
(23.65 KiB) Downloaded 614 times

Date AU → ISO.nwm
(23.2 KiB) Downloaded 578 times

Cheers,
Adrian
MacBook Pro (M1 Pro, 2021)
macOS Ventura
Nisus Writer user since 1996
adryan
Posts: 561
Joined: 2014-02-08 12:57:03
Location: Australia

Re: Table Format for Sort by date?

Post by adryan »

G’day, all

Date ISO → AU.nwm
(22.78 KiB) Downloaded 583 times

Date ISO → AU Ordinal.nwm
(23.65 KiB) Downloaded 577 times

Cheers,
Adrian
MacBook Pro (M1 Pro, 2021)
macOS Ventura
Nisus Writer user since 1996
withoutFeathers
Posts: 140
Joined: 2013-03-19 16:22:50

Re: Table Format for Sort by date?

Post by withoutFeathers »

@Philip,
Thanks for the ascend/descend and I'll test it. I understand your reluctance to address the blanks and the year-only, and I can deal with what's happening. I'm being sloppy leaving the blanks in there, and it doesn't really matter what day the software chooses if I only put the year anyway. :)

@Adrian,
I admit to being confused as to which you've changed and which you haven't today, in your uploads. And I'd like to avoid testing two versions against each other that are the same, or testing a version that's been superceded.

In other words, it appears you might have posted the same Macro more than once, but since there are no draft numbers I'm uncertain whether or not this is true.

Does your last series contain some reposted Macros that are identical to previous, or are those all new ones for those final posts?

wF
withoutFeathers
Posts: 140
Joined: 2013-03-19 16:22:50

Re: Table Format for Sort by date?

Post by withoutFeathers »

phspaelti wrote: 2019-09-01 22:48:34
Here is an updated version with the ascending/descending feature added:
Sort Table Rows By Selected Date Column.nwm
I find this works as expected in both directions.
Thank you again.

wF
adryan
Posts: 561
Joined: 2014-02-08 12:57:03
Location: Australia

Re: Table Format for Sort by date?

Post by adryan »

G'day, wF et al

All eight of those most recently uploaded macros are new versions. Version numbers would be a good idea. Next time!

You don't need to test them all if you don't want to. The Sort by Date macros are standalone macros, incorporating the code from the Date macros rather than merely referencing them. I've posted the Date macros because people might find them useful when no sorting is involved. You may just want to try the Sort by Date US macro.

Cheers,
Adrian
MacBook Pro (M1 Pro, 2021)
macOS Ventura
Nisus Writer user since 1996
withoutFeathers
Posts: 140
Joined: 2013-03-19 16:22:50

Re: Table Format for Sort by date?

Post by withoutFeathers »

adryan wrote: 2019-09-02 14:09:17 You don't need to test them all if you don't want to....--snip--... You may just want to try the Sort by Date US macro.
Hi Adrian,
I'm a bit OCD, so I have to finish the parts that apply to me. :)

So I tested four of your final posted bunch; here are the results:

Method (& Results):
Since my dates are variable and mostly non-US abbreviations:
1. Change dates to ISO using Philip's stand-alone 'Date->ISO'.
2. reverse them, ISO to US using Adrian’s two:
A. standard. (WORKS :) )
B. Ordinal. (WORKS :) )
3. Check that Adrian’s US to ISO works. (WORKS :) )
4. Check if Adrian’s Sort by US date works. (No, still gets stuck after changing them to ISO. Doesn’t sort table rows. )

Note that the error in #4 says it’s expecting to sort by paragraph:
“Unknown menu item path:
Edit:Transform Paragraphs:Sort Ascending (A-Z)“

Perhaps your “Sort by Date” macro isn’t designed to be usable in a table? My misunderstanding if so.
Should it only be used in a series of paragraphs?


wF
adryan
Posts: 561
Joined: 2014-02-08 12:57:03
Location: Australia

Re: Table Format for Sort by date?

Post by adryan »

G'day, wF et al

It sorts Tables for me, without any error messages.

Are you using NWP 3.0.3? That command is definitely there in the Edit hierarchical menu.

Cheers,
Adrian
MacBook Pro (M1 Pro, 2021)
macOS Ventura
Nisus Writer user since 1996
Post Reply