Multi-row Formula tool is one of my favorite tools because it's very convenient in iterating each row in the table. I can control the data in the row from the previous rows, the current row and the next rows. In the final interview at The Data School New York, the Multi-row Formula helped me to solve the problem. I wrote an article about how I use the Multi-row Formula tool to solve the problem on LinkedIn. You can also read it here.
In this blog, I'm going to show how to apply mathematics into solving the problem. Specifically, I use Modular to group the data in the table. I split this blog into 3 parts:
1/ What is Modular in mathematics?
2/ Using Modular to name the title and grouping data with Multi-row Formua tool in Alteryx
3/ Application of Modular in Cryptography
1/ What is Modular in Mathematics?
Before using Modular in preparing data, I explain a little bit about modular and its properties.
- In the example below (Image 1), I have a dividend 11 and a divisor 3. If I take 11 divided by 3, I got 3 and the remainder is 2. So 2 is the result of 11 mod 3.
- In Modular Arithmetic, according to the Wikipedia page, the modular is defined as:
"Given an integer n > 1, called a modulus, two integers a and b are said to be congruent modulo n, if n is a divisor of their difference (that is, if there is an integer k such that a − b = kn)." 0 ≤ a < n
In the example below, a= 2; b=11 and n=3. Based on the definition above, I get 2-11 = k(3) => -9 = (-3)*3. k=-3 is an integer. It satisfied the definition, so 2 is congruent with 11 mod 3. There are many properties about the modular in Wikipedia hyperlink page above, but I can't list all here.
- In Alteryx, if I type Mod(11,3), I also get the result 2.
2/ Using Modular to Name the Title and Grouping Data in Alteryx
Assume that I need the result as Image 2 after scraping data from a webpage.
However, after downloading and cleaning the data, I got the result same as Image 3. How can I get the table as in Image 2? In this blog, I will show how to name the title and group data.
This technique is used when the rows/ columns appear in cycle. As we can see, Records from 1 to 4 are in 1 row. Then, it repeats again for Records from 5 to 8. Every 4 records is in one row.
Let's get started!
In Image 3 above, the result doesn't have a RecordID field. The RecordID will help me to do calculation later. So I will drag the RecordID tool to connect with my Input data and set default setting. (Image 4)
Now, I need a Title column to name each row in the result in Image 4. As I analyzed from the beginning, every 4 records are in one row. It repeats again and again, so it's good to do with modular. The divisor should be 4 and the dividend is [RecordID] field.
If the result of RecordID mod 4 is 1, then return "From". If the result is 2, then return "To". If result is 3, then "Sales". Otherwise, it would be "Profit". Now, I got the title with the name of each row. (Image 5)
Now, I already got the title for each column. I need to transform data from Column to Row, so I need to group 4 rows into 1 row when I transform from Column to Row. To do that, I also use Modular again but in Multi-row Formula tool. (Image 6)
I created a new [Group] field type Integer. The Expression is:
IF Mod([RecordID],4)=1
THEN [Row-1:Group]+1
ELSE [Row-1:Group]
ENDIF
It means that If RecordID mod 4 equals 1, then Group number at the current row equals the Group number in the previous row plus 1. Otherwise, I will keep the Group number in the previous row.
Then, run it. I will get the Group number as I want with 4 lines of code. Now, I only need to transform the data from Column to Row with the Cross Tab tool.
I will group the data with Group field. The header of each column is Title field. and Value for each column is Data field. I chose Concatenate to Aggregating values with comma separator. I can use Select tool to reorder the field.
In this part, I show how to name the title for each row and how to group data before transforming data. I wrote an article on LinkedIn about Web Scraping in Alteryx. I also used this technique. You can read it here.
3/ Application of Modular in Cryptography
In the daily life, modular arithmetic is used to detect the errors in identification numbers. For example, numbers in the bank account, credit card,... If one or 2 numbers are incorrect, it would be a big problem.
Another application of Modular is using in Cryptography. I found it's very interesting to encrypt or decrypt the message. Assume that there are 2 people that want to communicate each other, they don't want other people know the message because they are in a public area. They only need to exchange their key to decrypt the message.
Diffie-Hellman and RSA Algorithm are used in Cryptography. If you are curious how it works and would like to know the difference between those algorithm, you can watch a Youtube video here.
Above is some shaped made from Modular Arithmetic from dprogrammer.org.
In conclusion, applying mathematics in preparing data will help to solve the problem faster in some cases. In this blog, I introduced how modular helps me to solve the problem and its application in the daily life. I hope you enjoy this blog.