Swap data using table calculations

  • 16 April 2024
  • 0 replies
  • 47 views

Userlevel 4
Badge +13

Recently, one of our customers who is leveraging risk insights for log ingestion ran into a problem. For certain dates of the month (April 14 & 15), the ingestion swapped "Bytes Uploaded" and "Bytes Downloaded." How can they leverage Advanced Analytics to swap the data back so that their monthly reporting would be accurate? Here is one solution with Table Calculations. 

 

The first step is to create a Custom Dimension with command:

 

concat(${page_event.event_timestamp_date},"") 

 

This converts the format of all “Event Dates” from “Timestamp” to “String,” which helps avoid potential errors in our Table Calculations. We name the dimension Event Date - String and then save it.

W8AHSiTEscqJ4gCQCLR0ptzH31r1Gel1qeqeB3n8bEfrIF_8HWwJIxhH-TEzHXDL7F8W0I0Y8FdRs_DXrnm5Abzm7o3I0SKUx_Pt33R6OpbWd-cvNO60PNpHmSHJ_ktS2KEnmYhJl2u6TOLYwCAF6qs

 

The second step is to create Table Calculations which swap the data for particular dates (April 14 & 15 in this case). Let’s start with the Table Calculation swapping “Bytes Uploaded” to “Bytes Downloaded.”

 

We create a Table Calculation with command:

 

if(

  ${event_date_string}="2024-04-14"

  OR

  ${event_date_string}="2024-04-15",

  ${page_event.sum_bytes_uploaded},

  ${page_event.sum_bytes_downloaded}

)

 

This swaps “Bytes Uploaded” to “Bytes Downloaded” for April 14 & 15, 2024.

yZ4RR4Qzdy4AkCxfDO8aGe7tlAEpqMAJhU6tdP8rLN3M_aQCPRajIBEKUfeL28dX9ZavgImhF_2M9IiCqph6ARVHzPkgwe_RT7JGMdtD4qV3J8vL1E1-Q3uCZu1SeV_7y1JfJAihXeOF9EvpEv90BoI

 

We name the calculation Swapped - Bytes Downloaded and then save it. By applying the calculation to the visual, we can see that “Bytes Uploaded” has been swapped to “Bytes Downloaded” for April 14 & 25 only. The data for other dates is not swapped.

LVYb_sWP7ieIbr3xJXp7AhotQdpX6dHMAD0fBOe9TWRo2hvPsw8DSWH3zyona4Q3uWtb8utKj-iwocTA5PqyWpgQ4C0SC466A9UZdA2tYMn7y19vng4ynmlF6uQgcVnattcuZWxxgLPdYveBFWuNdnY

hRM1W5ft7xsNqxy_uRNwGhU1qaZ8mruFrxeZ97yWpTe7-bXQ8yvIVAuW59ECUzTsis4e187auIyUqCb0mdd8T2aO3g91eHsGJW34PCHHiX5hJFTpK0XQwdG7XrCVNWzFuqSypMHUaKiv90KMnWH4qYg

 

We now create another Table Calculation with command:

 

if(

  ${event_date_string}="2024-04-14"

  OR

  ${event_date_string}="2024-04-15",

  ${page_event.sum_bytes_downloaded},

  ${page_event.sum_bytes_uploaded}

)

 

This swaps “Bytes Downloaded” to “Bytes Uploaded” for April 14 & 15, 2024.

z_1cM3PTqOCxiAniGG8m2M71xUmIQoXLehMfZcESajpacSrT62wRSoHSG_78zq9n_9UlFhb6DJ61CX5X6uyY4JUlwc6xhud3auLPTbbg8Z4gPsO3ORxZhyvmLGUlG1BRhNMQJlBgEdO4kcG9y6JRo4o

 

We name the calculation Swapped - Bytes Uploaded and then save it. By applying the calculation, we can see that “Bytes Downloaded” has been swapped to “Bytes Uploaded” for April 14 & 25 only. The data for other dates is not swapped.

SiYGJxYHBOl1-6mAtoUC8MLtLUvDBqK0_HGsQtnlKlTNZJPRGDfrRS2xFd06zqiGIovABTszjy5R21_81Vi4Umx8azTMeLK8EQbsNdJBbhGIPELlbXtXkuIEnTZTQSqXt3DYh5k45x-fGcUuCNzpJww

28j0hU2OCEK4XKwUfH2Gi7LXlMgAr8SkRK8MPObKO1BhgAiFtLZXiQeogguQXRJsEeiU-hwvrQ9ie8YSRb0AQpbRLmedVJ3pR6HuHb2Ujyjem4qmTlEjGLxKo4vi4CIIF4OFWSkbXK72vZPUUrfHDMI

 

You can also further customize the Table Calculations by changing the dates based on your use cases. The only thing you need to change is the command:

 

${event_date_string}="2024-04-14"

OR

${event_date_string}="2024-04-15",

 

You can duplicate the command and add any additional dates. For example:

 

${event_date_string}="2024-04-14"

OR

${event_date_string}="2024-04-15"

OR

${event_date_string}="2024-04-16"

OR

${event_date_string}="2024-04-17",

 

By doing so, the Table Calculations will then swap the data for all additional dates added.

 

The visual template is attached below. Feel free to import and view it in your own environment. Let us know if you have any questions!


0 replies

Be the first to reply!

Reply