Let’s say we import a whole bunch of data through the automatic reconciliation function in a reconciliation template. To make it simple, I’ll import all parts of a car with their expenses from a certain excel-sheet I’ve gotten from my client, like this:
Because of all double types in the excel-sheet, I’d like to group these on type to see what amount correspond with what type. I’ll even try to make type Wheels (with capital) and wheels (without capital) the same type.
To get something like this for instance:
we can use this code:
{% stripnewlines %}
| Name
| Value
{% newline %}
|----70%----
|----30%----#+
{% newline %}
{% fori item in custom.items %}
| {% input item.name %}
| {% input item.value as:currency %}
{% assign name = item.name | downcase | capitalize %}
{% capture current_total %}total_{{ name }}{% endcapture %}
{% assign [current_total] = [current_total]+item.value %}
{% assign items_array = items_array | append:name | append:";" %}
{% newline %}
{% endfori %}
{% endstripnewlines %}
{% assign items_array = items_array | split:";" | uniq %}
<br />
{% stripnewlines %}
| Group
| Value
{% newline %}
|----70%----
|----30%----#+
{% for item in items_array %}
{% newline %}
{% capture current_total %}total_{{ item }}{% endcapture %}
| {{ item }} | {{ [current_total] | currency }}
{% endfor %}
{% endstripnewlines %}
Let’s break this down:
{% assign name = item.name | downcase | capitalize %}
In each loop we’ll create a variable that takes the imported name, downcase it (no capitals) with the downcase-method, and then capitalize it (so every name will start with a capital, and all the rest is downcase. A plus here is that Wheels and wheels will get the same variable name: Wheels).
{% capture current_total %}total_{{ name }}{% endcapture %}
Further in each loop, we’ll create another variable called current_total. This is created by text total_ and adding the variable name to it.
So in each loop you’ll get something like total_Wheels for instance or total_Lights for the variable {{ current_total }}. However, we want those exact names act like another variable that we assign each corresponding value to it:
{% assign [current_total] = [current_total]+item.value %}
And that’s what the are for. In each loop we’ll create a variable called [current_total], assign it to the same variable [current_total] and add a value to it.
Let’s dive into this a little deeper:
In the first loop the outcome of {{ current_total }} is :
total_Wheels
In the second loop the outcome of {{ current_total }} is :
total_Belts
In the third :
total_Windshields
…
We want those variable names to use as other variables to display the value for each type (variable).
So we take the name of a variable and use it like this [current_total] to go deeper into that variable; think of it like this:
{{ current_total }} displays the output of my capture
{{ [current_total] }} will use the name of my original variable but will display something else (other than the capture I originally assigned it to).
So, in the first loop, before the second assign, the outcome of {{ [current_total] }} is nothing.
After the assign {% assign [current_total] = [current_total]+item.value %} however, the “nothing” (if I may call it like that) gets added to it, plus the value of 5.600 gets added.
Meaning, the outcome of {{ [current_total] }} in the first loop (after the assign) is: 5.600,00
In the second loop same thing happens:
{{ [current_total] }} is nothing (because it’s linked to the variable total_Belts, but after the assign the value of 2.500,00 gets added.
In the fifth loop, we again have the variable {{ current_total }} which has as output = total_Wheels (the same as the first loop!).
So in the assign {% assign [current_total] = [current_total]+item.value %} the outcome of {{ [current_total] }} is actually already 5.600,00 (because that was take into the variable [current_total] of the first loop!). So in the fifth loop, after the assign, the variable [current_total] gets assigned to [current_total] again (5.600,00 actually) plus the value added of the fifth loop 800,00 meaning the outcome of [current_total] is 5.600 + 800 = 6.400,00
We will use these variables later on to group on!
{% assign items_array = items_array | append:name | append:";" %}
We’ll create an array, so we can loop over it later on. In each loop we will take the variable name and append ; to it.
So as outcome we’ll get something like this:
Wheels;Belts;Windshield;Lights;Wheels;Belts;;
This is not exactly what we want, because we want the unique types.
{% assign items_array = items_array | split:";" | uniq %}
With the split-function on ; we’ll split that array up (so we can loop over each segment of the array), but with the uniq method duplicate segments (such as Wheels) will get removed from the array.
The outcome now is this of items_array :
WheelsBeltsWindshieldLights
Now we’ll display all types and values with a forloop over that array:
{% stripnewlines %}
| Group
| Value
{% newline %}
|----70%----
|----30%----#+
{% for item in items_array %}
{% newline %}
{% capture current_total %}total_{{ item }}{% endcapture %}
| {{ item }} | {{ [current_total] | currency }}
{% endfor %}
{% endstripnewlines %}
Let’s get into this as well:
{% for item in items_array %}
The value of {{ item }} (Wheels, Belts, Windshield, Lights) will be used to create the variables we used in the fori-loop:
{% capture current_total %}total_{{ item }}{% endcapture %}
This is exactly the same thing as we did in the fori-loop.
{{ [current_total] | currency }}
So the outcome of {{ [current_total] | currency }} is actually those variables we created in the fori-loop where we added each value into it.
Credits where credits are due: thanks @SamVanEenoo for this great code you provided 