How to Parse and Display CIBIL Payment History in Bold BI from JSON

I am trying to parse a JSON response from a CIBIL report and display the paymentHistory field in Bold BI as a tabular visualisation. The goal is to show the "Days Past Due/Asset Classification" over a 36-month period (left to right). Below is an example of the format I want to achieve:

DAYS PAST DUE:

000 000 055 024 000 000 000 000 000 000 000 000 000 000 024 024 084 113 113 114 113 116 115 115 116 085 055 024 000 000

DATE (MM-YY):

10-24 09-24 08-24 07-24 06-24 05-24 04-24 03-24 02-24 01-24 12-23 11-23 10-23 09-23 08-23 07-23 06-23 05-23 04-23 03-23 02-23 01-23 12-22 11-22 10-22 09-22 08-22 07-22 06-22 05-22

Here’s a snippet of the JSON data:

{
  "index": "T004",
  "memberShortName": "NOT DISCLOSED",
  "accountType": "05",
  "dateOpened": "23052022",
  "paymentHistory": "000000055024000000000000000000000000000000024024084113113114113116115115116085055024000000",
  "paymentStartDate": "01102024",
  "paymentEndDate": "01052022"
}

I want to extract the paymentHistory string and format it to display the "Days Past Due" along with the respective months (MM-YY) in Bold BI. The paymentHistory is a string where each set of three digits represents the number of days past due for a given month.

What I Tried

With the limited expressions available in Bold BI, I used the SUBSTRING expression to split the paymentHistory into 3-digit blocks representing the days past due. However, I couldn’t figure out how to display the months side by side along with the values. This made it difficult to properly align the "Days Past Due" with the corresponding months.

What I Expected

I expected to dynamically extract the paymentHistory string from the JSON data and split it into 3-digit blocks, each representing the "Days Past Due" for each month. I wanted these values to be automatically aligned with their corresponding months (MM-YY) in a tabular format within Bold BI. Specifically, I hoped to see a flexible solution that could adapt to different lengths of paymentHistory without having to manually adjust the code for different datasets. Ideally, this would allow me to easily visualize the payment history data in a clear and organized manner.

You need to sign in to view this answers