I need to select the rows based on the
latest 'Payment_due_date' column value.
Let's say I have a Deski report with 3 columns:
Payment_due_date,
Cust_Name,
Oustanding_Amt.
Report is like this:
=====================================
Payment_due_date,Cust_Name,Oustanding_Amt
02/28/11 , Bob, 115
03/31/11 , Bob, 90
02/28/11 , Mike, 120
02/28/11 , Susan, 220
05/31/11 , Bob, 80,
03/31/11 , Susan, 70
05/31/11 , Mike, 220
05/31/11 , Susan, 120
04/30/11 , Susan, 130
So,we would like to see (based on latest Payment_due_date)
==================
05/31/11 , Bob, 80,
05/31/11 , Mike, 220
05/31/11 , Susan, 120
Oustanding_Amt comes as SUM(Oustanding_Amt) from Universe.
If we use Max(Payment_due_date),we do get one row but then,
the column 'Oustanding_Amt' sums up.
Not allowed to use do SQL Override .Context Operators
aren't working.
Thanks in advance:)
- 3 Answers
- 5728 Views
- I also Faced
- E-Mail Answers
Answers were Sorted based on User's Feedback
Answer / rajitha praburam
You can get the required results by using RANK function.
Create an object 'Rank' with Number Data type and the below
select option.
RANK() OVER (PARTITION BY CUST_NAME ORDER BY
PAYMENT_DUE_DATE DESC)
In the report, use report level filter add Rank = 1
condition.
| Is This Answer Correct ? | 1 Yes | 0 No |
Answer / rahul
You can achieve this by having a condition.
i.e put payment_date in condition panel-->select operator
as "equal to"-->operand "calculation"-->follow the
steps"select object(payement_date)"-->select function
(maximum)-->Globally-->for each value of one or more objects
(cust name)--finish
| Is This Answer Correct ? | 0 Yes | 0 No |
Answer / gs2kumar@gmail.com
Max(Payment_due_date) use this in report level create a variable or in cell.
| Is This Answer Correct ? | 0 Yes | 0 No |
HOW TO SAVE UNIVERSES AND DOCUMENTS IN THE REPOSITORY?
Explain is there any other repository domains rather than universe, security, document?
What is the prm file ? can anybody explain me ?
when you have three fact table and there is no relationship between them. How you will tackle the issue.
can any body tell me how to create Hyperlinks in BO XI R2?
define universe parameters?
10.why we are going to data warehouse?
how do we apply sql query on selected variables?
How to create a Pop-up in BOXIR2. Ex:- A class is having an object as "AGE", whenever this object is used in the reporting level automatically a pop- up will be coming which shows a prompt as "Please use the object DOB".
Can any please explain FOR EACH AND FOR ALL?
What are the general issues in migration process?
Can some one say abt "ALL" in prompt .. How to get the word "ALL" in the prompt along with the list of values? Plz give me the steps?
Teradata 
