Creating a table with unique values and sum_if counts

beck · March 2, 2021, 8:09pm

I’ve hardcoded a table to display the count for each tag in a container of notes. This particular container has 853 children, 23 tags and they distribute like so:

The code for any given row looks like this:
<tr><td>changeTech</td><td>^action($MyNumber=sum_if(children, $Tags.format(";").icontains("changeTech"),1))^^value($MyNumber)^</td></tr>

What might I do to create this as an export template? @satikusala I’m wondering if you might already have a video that would help? I know I saw one where you have a table print out the values for marketing emails… but what stumped me here was (1) only creating a row for .unique values of $Tags and (2) grabbing a given row’s tag to calculate that row’s sum_if() count.

satikusala · March 2, 2021, 9:14pm

Hey @beck, the table you’re referring would not apply in this context as that is used for generating a table from attributes. In this case, you’re just looking to pull in a value.

Is the table above not what you want, or are you looking for something different? I don’t really understand your data enough to intuit what you’re trying to accomplish. If you’d like, we can hop on a zoom and get this sorted out quick.

eastgate · March 2, 2021, 9:24pm

I may be missing something, but I think this can be simpler. First, I don’t think there’s any need to convert $Tags to a string; we’re looking for a specific tag and we know how its spelled. So that gives us:

<tr>
<td>changeTech</td>
<td>^action($MyNumber=sum_if(children, $Tags.contains("changeTech"),1))^
^value($MyNumber)^</td>
</tr>

Next, I think it’s a bit clearer to move the action away from the table.

^action($MyNumber=sum_if(children, $Tags.contains("changeTech"),1))^
<!-- compute the number of children with this row’s tag -->
<tr>
<td>changeTech</td>
<td>^value($MyNumber)^</td>
</tr>

mwra · March 3, 2021, 10:40am

As “changeTech” is only one of the 23 tags we need summed, one way to approach this is to first collect all the discrete values for $Tags in the container’s 853 children:

$MyList = values(children,"Tags").sort;

$MyList is now an alphabetically sorted list of the 23 tags. Within the <table> we write each row out of the loop. Put together, we get this template to use for the container:

<!DOCTYPE html>
<html>
<head>
	<meta http-equiv="content-type" content="text/html; charset=utf-8">
	<title>^title^</title>
</head>
<body>
<h1>^title^</h1>
^text^
<table>
^action(
$MyString=;
$MyList = values(children,"Tags").sort;
$MyList.each(aTag){
   var vCount(sum_if(children, $Tags.contains(aTag),1));
   $MyString = $MyString +"<tr>\n<td>"+aTag+"</td>\n";
   $MyString = $MyString + "<td>"+vCount+"</td>\n</tr>\n";
};)^^value($MyString)^^action($MyString=;$MyList=;)^</table>
</body>
</html>

Tested in v8.9.2.

beck · March 2, 2021, 10:53pm

I’m going to have to list you as a byline in my dissertation, @mwra.

mwra · March 2, 2021, 10:58pm

Tinderbox definitely got mention through out my thesis. The second large piece of (mixed method) research in it would have been very difficult in a tool with less flexibility and support for emergent structure. Good luck with the writing!

satikusala · March 3, 2021, 4:07am

@mwra, brilliant solution!

mwra · March 3, 2021, 10:42am

@beck ops small error in template (corrected above for later readers). Last part:

^value($MyString)^^value($MyString=;)^

should be

^value($MyString)^^action($MyString=;$MyList=;)^

The last is simply clearing out ‘in-template’ cached strings/lists.

mwra · March 3, 2021, 8:29pm

It’s annoying when people ask interesting questions. I’ve extended the output in this demo file: lists-to-tables.tbx (149.9 KB) [EDIT: small action code only code tweak - link to file and explanation in my next post below]

It has 5 templates with names starting ‘Table’. “Table” is the solution already shown above. The others are 4 more lexically sorted tags (forward (A-Z) and reverse (Z-A)), and numerically sorted (forward (ascending value) and reverse (descending value)). The eagle-eyed will not the sort/reverse order hinges on one simple addition of a .reverse operator. Before anyone asks, I’ve not figured a two tier sort, e.g. descending count with lexical tag sort for same-count tags.

I did discover a ~~bug (which I’ve reported)~~ edge case issue with the String.toNumber operator Edit wored around in updated TBX]~~(it doesn’t work unless the value is passed to a Number-type object, e.g. not a variable!)~~. The tags sort uses a look-up table, as a way to ~~work around the bug~~ avoid that issue.

The notes have lots of attributes as this started out in my '‘starter’ file though I’ve deleted lots of un-needed stuff.

The demo works using the templates applied to Test cell and the $Tags data of its children:

templates:

lists-to-tables.tbx 2021-03-03 13-07-33

So, for instance, here is the preview of using template “Table tag sort reverse lookup” (i.e. tags in reverse Z-A order):

Great, a whole morning of data analysis avoided. Back to work…

beck · March 3, 2021, 3:46pm

This is awesome!

Now to ponder some more annoying questions…

mwra · March 3, 2021, 8:26pm

I’ve just updated the above demo file: lists-to-tables.tbx (149.9 KB)

Not HTML output change but the “Table count sort” and “Table count sort reverse” templates have had a code tweak to obviate the use of $MyNumber (by chaining .format("l") to the .toNumber operator.

The earlier ‘bug’ transpires to not be that but an unexpected behaviour. The code is essentially String+Number-from-String+String. As Tinderbox type coercion works to that String+Number => String. Tinderbox sees we are coercing a string to a number but adding it to a string, so uses the source string instead. The additional format command forces the padding (text) digits to be dropped.