Copy certain rows from tables that match certain criteria to a new table

#1

I have many tiddlers containing tables of Customers. I need to find all rows with, for example, Customer = Smith and copy the rows into a new table, one column has quantities which I need to sum according to the Measure column, the measure may be either Hogsheads or Bushels.
The tables have the following header. |Customer|Date|Qty|Measure|Location|Notes|h
There may be an existing solution or close but in any case I could use some help on the best approach to solving the problem.
Peter

#2

Are these static tables with the customer information hardcoded in the tiddler’s text? Or are they dynamic tables with content in separate tiddlers joined into a table with a filter?

That is, do your tiddlers look like this?:

| Customer | Date            | Qty | Measure | Location | Notes |h
| Smith    | 2025-03-12      | 10  | 6.35    | Loc 1    | n/a   |
| Jones    | 2025-03-13      | 22  | 1.48    | Loc 2    | n/a   |
| Smith    | 2025-03-13      | 9   | 7.12    | Loc 1    | n/a   |

or like this?:

<table>
<tr><th>Customer</th><th>Date</th><th>Qty</th><th>Measure</th><th>Location</th><th>Notes</th></tr>
<$list filter=[tag[Measurement]]>
<tr>
  <td>{{!!customer}}</td>
  <td>{{!!date}}</td>
  <td>{{!!qty}}</td>
  <td>{{!!measure}}</td>
  <td>{{!!location}}</td>
  <td>{{!!notes}}</td> 
</tr>
</$list>
</table>

If it’s the latter, there are plenty of useful ways to help, although I think we’d need to know more about your tiddler structure.

If it’s the former, I’m afraid it’s going to get uglier I think you would need to do some parsing of the markup… although I’d love to hear someone prove me wrong.

#3

Thank you for responding Scott. I’m afraid the tables are the static hardcoded variety, so i suppose I will need to use regex somehow to copy the appropriate rows into new tables. There are many tiddlers with perhaps an average of 30 entries, on average, in each table. I just can’t see the best way to get started and I don’t know what is possible within tiddlywiki. Any help in this regard will gratefully received.

#4

@Scott_Sauyet is correct in that many things are possible if you have your data broken down into one tiddler per row and each column being a field in each tiddler.

To help you get to that state, here is a generic wikitable parser that will convert a chosen tiddler containing a wikitable into a series of row tiddlers that are tagged with that chosen tiddlers name. Copy and paste the following in to a new tiddler.

\define parsetable()
<$let tt={{{[{!!tabletiddler}]}}}>
<$set name="header" filter="[<tt>get[text]splitregexp[\n]suffix[|h]first[]split[|]rest[]butlast[]trim[]]">
<$list filter="[<tt>get[text]splitregexp[\n]!suffix[|h]]" counter="linenumber" variable="linecontent">
<$action-createtiddler $basetitle={{{[<tt>] [[Row]] [<linenumber>] +[join[ ]]}}}>
<$action-listops $tiddler=<<createTiddler-title>> $field=tags $subfilter="[<tt>]"/>
<$list filter="[<linecontent>split[|]rest[]butlast[]trim[]]" variable="cellcontent" counter="cellnumber">
<$action-setfield $tiddler=<<createTiddler-title>> $field={{{[enlist<header>nth<cellnumber>]}}} $value=<<cellcontent>>/>
</$list>
</$action-createtiddler>
</$list>
</$set>
</$let>
\end

<$select field=tabletiddler>
<$list filter='[all[tiddlers]]' variable=listitem>
<$list filter='[<listitem>get[text]splitregexp[\n]suffix[|h]first[]]'>
<option><$view tiddler=<<listitem>> field='title'/></option>
</$list>
</$list>
</$select>

<$button actions=<<parsetable>> >Parse Table</$button>

This seems to work in my simple testing. There are definite limitations though:

  • it will only handle tiddlers containing a single table with the first header row defining the fields (all other header rows will be ignored and multiple tables will be mashed together)
  • column header names need to be unique
  • no colspan or rowspan cells handled
  • multiple button pushes will keep making duplicates

Save a backup of your wiki before trying this as there are no guarantees that it won’t eat your data.

A simple sum of a “column” can be displayed like this:

{{{[tag{!!tabletiddler}get[Qty]sum[]]}}}

With some contortions you can handle mixed table unit conversions. Something like this:

{{{[tag{!!tabletiddler}Measure[Hogsheads]get[Qty]multiply<conversionfactor>] =[tag{!!tabletiddler}Measure[Bushels]get[Qty]] +[sum[]]  }}}

… but this should get you pointed in the right direction.

/Mike

1 Like
#5

Thank you Mike this looks like just the sort of thing I need; it will certainly get me started.
Peter

1 Like