为价值组计算MAX(DATE),其中值返回和返回 [英] Calculating MAX(DATE) for Value Groups Where Values Go Back and Forth
问题描述
ACCT_NUM CERT_ID代码日期所需产出
A 1 10 1/1/2007 1/1/2008
A 1 10 1/1/2008 1/1/2008
A 1 20 1/1/2009 1/1/2010
A 1 20 1/1/2010 1/1/2010
A 1 10 1/1/2011 1/1/2012
A 1 10 1/1/2012 1/1/2012
A 2 20 1/1/2007 1/1/2008
A 2 20 1/1/2008 1/1/2008
A 2 10 1/1/2009 1/1/2010
A 2 10 1/1/2010 1/1/2010
A 2 30 1/1/2011 1/1/2011
A 2 10 1/1/2012 1/1/2013
A 2 10 1/1/2013 1/1 / 2013
如你所见,我需要根据每组代码做一个MAX值(除了ACCT_NUM和CERT_ID)之前值更改。如果重复相同的值,则我需要再次为该组再次输入MAX数据。例如,对于1的CERT_ID,我无法对代码10的所有四行进行分组,以获得1/1/2012的MAX日期。我需要获得前两行的MAX,然后分别替换下一行的另一个MAX,因为之间有另一个代码。我正在Cognos Framework Manager中完成此工作。
Gurus,请指教。
CERT_ID的最大值为:
最大值(CERT_ID的日期)
如果您想要其他级别的最大值,您可以使用以下语法:
maximum(ACCT_NUM,CERT_ID,Code的日期)
最好的做法是在报告中分组和总结价值,而不是在框架管理器中。
I have another challenge that I am trying to resolve but unable to get the solution yet. Here is the scenario. Pardon the formatting if it messes up at the time of posting.
ACCT_NUM CERT_ID Code Date Desired Output
A 1 10 1/1/2007 1/1/2008
A 1 10 1/1/2008 1/1/2008
A 1 20 1/1/2009 1/1/2010
A 1 20 1/1/2010 1/1/2010
A 1 10 1/1/2011 1/1/2012
A 1 10 1/1/2012 1/1/2012
A 2 20 1/1/2007 1/1/2008
A 2 20 1/1/2008 1/1/2008
A 2 10 1/1/2009 1/1/2010
A 2 10 1/1/2010 1/1/2010
A 2 30 1/1/2011 1/1/2011
A 2 10 1/1/2012 1/1/2013
A 2 10 1/1/2013 1/1/2013
As you can see, I need to do a MAX on the date based on each group of code values (apart from ACCT_NUM and CERT_ID) before the value changes. If the same value repeats, I need to a MAX of the data again for that group separately. For example, for CERT_ID of '1', I cannot group all four rows of Code 10 to get a MAX date of 1/1/2012. I need to get the MAX for the first two rows and then another MAX for the next two rows separately since there is another code in between. I am trying to accomplish this in Cognos Framework Manager.
Gurus, please advise.
The syntax for getting the max value for CERT_ID is:
maximum(Date for CERT_ID)
If you want additional level/s for max you can use the following syntax:
maximum(Date for ACCT_NUM,CERT_ID,Code)
In general, it is best practice to group and summarize values in report, not in framework manager.
这篇关于为价值组计算MAX(DATE),其中值返回和返回的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!