英文:
Pentaho Spoon get -1 day formula of the next data entry
问题
希望你能帮我解决这个数据处理问题。我不知道哪个步骤可以解决这个问题。是用JavaScript吗？还是有一个简单的公式可以解决呢？
我有一个包含日期列的数据流，我想要另外添加一列，该列将获取每个日期条目的(date - 1)。请参见下面的详细信息：
审计日期
9/16/2021
9/17/2021
10/07/2021
10/17/2021
09/26/2021
这将是预期的输出：
审计日期 列 日期 -1 列
9/16/2021 9/16/2021
9/17/2021 10/06/2021
10/07/2021 10/16/2021
10/17/2021 09/25/2021
09/26/2021 最新日期
谢谢！
英文:
hope you can help me solve this data processing. I have no idea which step can solve this. Is it javascript? or is there a simple formula that can solve this
I have a stream that has date column and I want to have another column which will get the (date - 1) of each date entry. Please see the details below:
AuditDate
9/16/2021
9/17/2021
10/07/2021
10/17/2021
09/26/2021
This is will be the expected output
AuditDate Column Date -1 column
9/16/2021 9/16/2021
9/17/2021 10/06/2021
10/07/2021 10/16/2021
10/17/2021 09/25/2021
09/26/2021 Latest Date
Thanks!
答案1
得分: 2
你有Analytic query步骤，其中包括LEAD操作来检索下一行（或第二行、第三行... 下一行）。
您必须按顺序提供行，以便它可以找到您要查找的行。我不知道在您提供的示例中是否打算将2021年9月26日作为最后日期，所以2021年10月17日的Date -1列在审计日期之前。如果是这样的话，在我的示例中，您可以跳过Sort rows步骤。当我使用Analytic query步骤时，我通常会在之前添加一个Sort rows步骤。
一旦您获得了下一行中的AuditDate值，您可以计算前一天。因为尝试在空日期上计算前一天会导致错误，所以我还添加了一个If field value is null步骤，将LEAD日期赋予一个“不可能”的值，以便在应用Calculator步骤后，如果需要将其还原为null值，我可以快速识别它。
这是转换的屏幕截图：
英文:
You have the Analytic query step, with the LEAD operation to retrieve the next (or the second, third... next) row.
You have to provide the rows ordered, so it can find the row you are looking for, I don't know if in the example you provided it was intended that the 09/26/2021 date was the last date, so Date -1 column for 10/17/2021 is before the audit date. If it's so, you can skip the Sort rows in my example. When I use the Analytic query step I have a Sort rows step previously.
Once you have the value of AuditDate in next row, you calculate the day before. Because trying to calculate the previous day on a null date gave me error, I have also added a If field value is null step to give to the LEAD date an "impossible" value I can quickly identify in case I need to return it to a null value after applying the Calculator step.
This is a screenshot of the transformation:
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论