如何在Bold BI中解析并可视化CIBIL报告的paymentHistory数据

问题背景 #

需要解析CIBIL报告的JSON响应，在Bold BI中将paymentHistory字段以表格形式展示指定周期内的逾期天数/资产分类（从左到右对应最新到最早月份）。paymentHistory为连续字符串，每3位代表当月逾期天数，需同时匹配对应MM-YY格式的月份。此前手动用SUBSTRING拆分无法实现月份与数值的动态对齐，需一套适配任意长度paymentHistory的通用方案。

JSON数据示例：

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

期望展示效果：

逾期天数：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
日期（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

解决方案 #

1. 动态拆分paymentHistory为单月逾期天数 #

利用Bold BI的数据转换或计算字段功能实现自动拆分：

• 先计算总月份数：LEN(paymentHistory)/3（自动适配不同长度的字符串）
• 通过数据转换-拆分列功能，选择按固定宽度拆分，设置宽度为3，将paymentHistory拆分为多列
• 再使用转置功能，将拆分后的列转换为行结构，生成包含所有逾期天数的单列数据

若使用SQL计算字段（需将JSON数据导入支持SQL的数据源），可结合GENERATE_SERIES实现：

SELECT 
  SUBSTRING(paymentHistory, (s.value - 1)*3 + 1, 3) AS 逾期天数
FROM 
  your_table,
  GENERATE_SERIES(1, LEN(paymentHistory)/3) s(value)

2. 生成匹配的MM-YY月份序列 #

基于paymentStartDate（格式DDMMYYYY）生成逐月递减的月份：

• 先将起始日期转换为标准日期格式：TO_DATE(paymentStartDate, 'DDMMYYYY')
• 结合序列生成对应月份，格式化为MM-YY：

SELECT 
  TO_CHAR(ADD_MONTHS(TO_DATE(paymentStartDate, 'DDMMYYYY'), -(s.value - 1)), 'MM-YY') AS 月份
FROM 
  your_table,
  GENERATE_SERIES(1, LEN(paymentHistory)/3) s(value)

这个逻辑会自动从起始日期往前推对应月份，确保与逾期天数完全匹配。

3. 构建可视化组件 #

• 若要纵向表格展示：将月份和逾期天数拖入表格组件，设置月份按降序排序
• 若要横向对齐展示（如示例样式）：使用矩阵组件，将月份设为列分组，逾期天数设为值字段，即可实现从左到右的横向排列

4. 适配任意长度的paymentHistory #

整个方案完全依赖paymentHistory的长度动态计算，无需手动调整：

• 当字符串长度变化时，LEN(paymentHistory)/3自动更新月份数量
• 序列生成和拆分逻辑会同步调整，确保所有逾期天数和对应月份精准对齐

内容的提问来源于stack exchange，提问作者Nikhil Anil