How to skip over blanks in Coda?
Pro stuff: getting the next value in line
I noticed in my VAT database some countries with only two or three VAT variations like Danmark: 0% and 25%
The VAT is a sales tax that applies to the purchase of most goods and services, and must be collected and submitted by the merchant to the Denmark governmental revenue department.
Denmark’s VAT rate of 25% ranks as one of the ten highest VAT rates in the world. Countries with similar VAT rates include Iceland with a VAT of 26%.
On the other hand countries like Belgium have values for all variations, while the Netherlands has the reduced VAT (was 6% a few years ago) and skips the Reduced variation. When a VAT type has no value, it is blank and in this scenario we need the next in line, not the previous one. Governments need their tax! For the Netherlands this implies that a reduced VAT rate from Belgium is translated into a standard VAT in the Netherlands.
The problem we solve in this blog is how you find the next value in line?
To make the issue understandable and applicable outside the VAT context for which I need it, we focus on a simplified version of the matter. In case we find nothing — IsBlank() — we skip and take the next value in line and if there is no next value, we take the last one.
To make things easier — and possible — we add an extra column with a row index and we use this index to see which of the rows contain a value and which don’t. This permits us to aks for the letter at position 4 or 5 for example.
The formula is a filter like below:
Our next step is to use a text box to enter numbers. We reference it in the filter for oversight and flexibility:
The Ifblank() permits us to show a value once the outcome is blank. We took number 5, the answer should be ‘D’ but for now we have ‘create something’. It is this ‘something’ we consider as our little puzzle to solve.
The value for a blank is based on an existing value, thus on IsNotBlank(). This leads us to the formula below. We need a letter related to one of these positions.
We take the outcome of the filter and compare it to the value in the text box using a FormulaMap():
We notice 2 numbers greater than, because we see true, true. How can we get the first ‘true’ out of this range? It took me a a bit of time to see it and the fun is that once you see it, you wonder why you did not see before. Here we go:
The Find() function gives us the position of the first true, it is position 4 of the list that outputs the values that are not blank.
Once we have this position, it is time to apply it in a filter that takes into account this position in the list. Positions are referenced via Nth() and this is what you see below. We create a list of non blanks and we ask for the letter on the position we just found
This is quite a formule blending various functions to get something simple as the next value in line.
This brings us back to my initial request on finding the next in line VAT value for which i needed this formula as part of a a larger code snippet. The row index in the above part is replaced by a group index and we have some additional criteria, but it all boils down to a combination of Filters, Formulamap and Find (FFF).
The interesting thing in Coda is that you keep on learning and you even notice your own acceleration in solving puzzles of all sorts.
I hope you enjoyed this article. If you have questions feel free to reach out. Though this article is for free, my work (including advice) won’t be, but there is always room for a chat to see what can be done. Besides you find my (for free) contributions to the Coda Community and on Twitter
My name is Christiaan Huizer and I am the owner of Huizer Automation. A company specialized in serving SME in harvesting data and keeping it aligned. I am a Coda Consultant & Expert and rely mainly on Coda, Mailjet, Zapier & Paperform to get the job done.
