Horizontal data mapping

Most detail tables are created by looping through a series of items in the original data, extracting the same fields for each record. There are times, however, when all the values for the detail table are stored in a single field. Today, we’ll look at a simple process for extracting those values into a detail table.

What’s the use case?

Recently, one of my esteemed-colleagues-who-shall-remain-unnamed asked me why the Repeat step in the DataMapper has a checkbox option labeled No Goto step required. I explained that this allows the Repeat structure to loop through items without requiring a Goto step to move from item to item.

He replied that he already knew that – I think he may have rolled his eyes at me, not sure, we were having this discussion via chat – and that he was just wondering why that option was available for XML and JSON data files, since the Repeat step for those data files uses an implicit Goto step to move to the next item in the collection.

I was momentarily stumped… until I recalled that the option also applies to that implicit Goto step. Essentially, the option instructs the DataMapper to leave its data pointer at the same location for the duration of the loop. So that’s what I explained to him.

My still-esteemed-colleague then replied with a simple follow-up question: “Why would I wanna do that?“.

I went on to explain that sometimes, you need to loop through a series of data tokens that are all stored in the same field. It might be a list of comma-delimited items, or it could be a numeric value that specifies how many times a loop should run, for instance. So you would want the data pointer to remain in the same position while you extract each data token into a detail table.

“Do you have an actual use case for that?“, my slightly-annoying-but still-esteemed-colleague then asked…

Hence, this article.

Let’s talk hockey

We’ll use a hockey example because, well, I’m Canadian, so I had the choice between bacon and hockey, but I couldn’t think of any useful example that involves bacon.

Oh, and by the way, I’m talking about real hockey here. The kind that’s played on ice, with skates and protective equipment worn by oversized athletes who revel in crushing their opponents against wooden boards. I need to make that distinction for my same non-Canadian-who-thinks-hockey-is-played-on-grass-esteemed-colleague.

With that out of the way, let’s look at a data file that contains a list of all teams that have ever won the Stanley Cup between 1927 and 2023. If you don’t know what the Stanley Cup is, shame on you, but don’t worry, you should still be able to follow along. You’ll find a link to download all the resources used here at the bottom of this article.

[
  {"team": "Anaheim Ducks", "years": "2007"},
  {"team": "Boston Bruins", "years": "2011|1972|1970|1941|1939|1929"},
  {"team": "Calgary Flames", "years": "1989"},
  ...
  {"team": "Vegas Golden Knights", "years": "2023"},
  {"team": "Washington Capitals", "years": "2018"}
]

Here, we see that out of 23 teams who have won at least one Stanley Cup, the dreaded Boston Bruins have won it 6 times (nowhere near the Montreal Canadiens’ record 22 wins, but that’s something to gloat over at some other time… ), the latest of which was 2011. Having that kind of structure will make it easy to design a template that displays these values in a clean way, using a Handlebars custom helper.

Extracting the team names is simple enough: since the team objects are the immediate children of the containing array, we just loop on .*, extracting the .team property into the Teams detail table.

But now we want to extract a nested detail table so that, for each team, we have the list of years in which they have won the Cup. In the original data, those years are stored in a single property with a pipe-delimited format: 2011|1972|1970|1941|1939|1929. When the team has only won the Cup once, the property contains a single year. So we need to loop through the contents of that property and extract each value as a distinct record in the nested detail table.

And this is what I like to call a horizontal data mapping loop, in case you were wondering what the title of this article is all about. No, it’s not a technical term, it’s just a something I coined to reflect the fact that in this instance, we’re reading the data horizontally instead of the usual top-to-bottom direction.

In order to loop through those values horizontally, we have to split the years property into an array of values. The length of that array will be variable: in this instance, it will range from 1 to 22, depending on the number of times the team has won the Cup. Fortunately, the pipe separator allows us to easily split that property with

data.extract('.years').split("|")

This splits the property into as many array items as there are years stored in it. If there is no pipe symbol, the split() method will still return an array of 1 item, so we’re all good.

In order to loop through all the values, we want the DataMapper’s data pointer to remain in the same location until we’ve extracted each individual year. ITo do that, we add another Repeat step inside our first loop and specify that No Goto step is required. We also specify that the number of iterations the loop must run is equal to the number of values in the array obtained through the split() method:

This Maximum number of iterations value is critical: it instructs the inner loop to run for a fixed number of iterations, after which the DataMapper exits the inner loop and returns control to the outer loop.

Finally, in that inner loop, we extract each value in the array by using the internal loop counter that tells us on which iteration we currently are:

Here, we use the split() method again and we immediately target a specific item from the resulting array by using steps.currentLoopCounter-1 as the index (because the current loop counter is 1-based whereas JS arrays are 0-based).

Note! the steps.currentLoopCounter property always refers to the current loop. When used in an inner loop, it contains the iteration value for that inner loop and masks the current iteration for the outer loop. If, for some reason, you need to know – from inside the inner loop – the iteration value for the outer loop, store the outer loop’s iteration counter in a variable prior to entering the inner loop.

And there you have it: horizontal data mapping for hockey fans!

Conclusion

Once you understand it, the same technique can be used with any data type and it does not necessarily have to result in nested detail tables. There are some cases when a standard detail table can be created from a horizontal data mapping loop, or it can simply be that you need to tally all the values in a field before extracting the total as a single value. I’ll leave it up to you to find instances where this could be useful.

One last thing: many thanks to my back-to-being-much-esteemed-colleague who managed to make my Designer template work in 2 minutes after I wasted almost 45 infuriating minutes trying to get things to align properly!

Resources