klipfolio - 在klipfolio中从json中分组多个日期

Question

我有一个 JSON 格式的输入，格式如下：

[
  {
    "hash":"abcdefg",
    "Stage 1 Status":"Complete",
    "Stage 1 Completion":"2021-01-16T19:56:10+02:00",
    "Stage 2 Status":"Complete",
    "Stage 2 Completion":"2021-02-17T16:30:30+03:00",
    "Stage 3 Status":"Complete",
    "Stage 3 Completion":"2021-03-17T16:30:34+03:00"
  },
  {
    "hash":"klmnop",
    "Stage 1 Status":"Complete",
    "Stage 1 Completion":"2021-01-16T19:56:10+02:00",
    "Stage 2 Status":"Open",
    "Stage 2 Completion":"2021-02-17T16:30:34+03:00"
  },
  {
    "hash":"jklmn",
    "Stage 1 Status":"Complete",
    "Stage 1 Completion":"2021-01-16T19:56:10+02:00",
    "Stage 2 Status":"Lost",
    "Stage 2 Completion":"2021-07-17T16:30:30+03:00"
  }
]

我想在 klifolio 中制作一个 klip，向我展示每个月的已完成阶段，就像从上述数据得出的以下输出一样。

	一月	二月	行进
阶段1	3	0	0
第二阶段	0	1	0
第三阶段	0	0	1

数据是三年的，我需要在单独的表格中显示年数。klipfolio 的新手，所以到目前为止，我已经为表格列标题和行标签构建了数组，但我在查询时遇到了困难。

标签： klipfolio

Answer 1

要在 Klipfolio 中完成此操作，您需要首先将 JSON 数据组织成柱状结构，以便对其执行查询。为此，您需要一组用于状态、完成和阶段名称的数据。

1. 对于舞台名称，您可以使用kf:names和LEFT()函数。这看起来像：

   LEFT(@kf:names(/,FALSE),7)

默认情况下，kf:names 按字母顺序返回数据，FALSE 参数按 JSON 结构从上到下的顺序返回数据。

LEFT() 将返回最左边的 7 个字符，它只会返回“Stage 1”而不是“Stage 1 Status”

2. 要返回日期，需要通配符选择器以及 contains() 函数来返回名称为 contains “Completion”的字段的任何值。

   @/*[contains(name(),'Completion')]

3. 最后，要返回状态，您需要执行与上述相同的操作，但搜索包含“状态”的字段名称。

   @/*[contains(name(),'Status')]

4. 从这里你应该有 3 个可以查询的数据数组。返回 JSON 结构中所有字段名称的第一个查询首先需要过滤以返回与日期和状态数组相同数量的项目。您可以使用SELECT()仅返回具有“完成”或“状态”的字段，因为我们知道这些字段与值之间存在一对一的关系。

SELECT(LEFT(@kf:names(/,FALSE),7),CONTAINS(@kf:names(/,FALSE),"Status"))

这将返回 7 项以匹配 7 项日期和状态。

现在数据的形状正确，可以使用 SELECT()、 AND()、GROUP()和COUNTDISTINCT()函数在每列中使用LOOKUP()来对齐每个阶段已完成记录的计数。

首先，结果在 LOOKUP() 的第一个参数中引用具有阶段名称的列。然后使用 SELECT() 筛选仅属于特定月份的已完成阶段。对于日期，您需要使用 DATE() 转换为 unix 时间，然后使用 DATEVALUE() 转换为汇总为一个月的内容，例如“yyyyMM”格式，如果日期在一月，则返回“202101”今年的。

SELECT() 看起来像这样：

SELECT(SELECT(LEFT(@kf:names(/,FALSE),7),CONTAINS(@kf:names(/,FALSE),"Status")),AND(DATEVALUE(DATE(@/*[contains(name(),'Completion')],"yyyy-MM-dd"),"yyyyMM")="202101",@/*[contains(name(),'Status')]="Complete"))

在第二个参数中，将 GROUP() 包裹在 SELECT() 周围以对类似的值进行分组，并在 LOOKUP() 的第三个参数中将 SELECT() 与 COUNTDISTINCT() 包裹起来以计算每组的项目。2021 年 1 月列的整个公式如下所示：

LOOKUP(&Stages,GROUP(SELECT( SELECT(LEFT(@kf:names(/,FALSE),7),CONTAINS(@kf:names(/,FALSE),"Status")), AND(DATEVALUE(DATE(@/*[contains(name(),'Completion')],"yyyy-MM-dd"),"yyyyMM")="202101", @/*[contains(name(),'Status')]="Complete"))), COUNTDISTINCT(SELECT( SELECT(LEFT(@kf:names(/,FALSE),7),CONTAINS(@kf:names(/,FALSE),"Status")), AND(DATEVALUE(DATE(@/*[contains(name(),'Completion')],"yyyy-MM-dd"),"yyyyMM")="202101", @/*[contains(name(),'Status')]="Complete"))))

从那里，您可以将公式中的 202101 更改为任何 yyyyMM 以返回特定月份数据 IE。对于下一列，2 月将是 202102。