Yeah I had the same thoughts, but the error message specifically pointed to the fact that the Installation Type did not appear in "either an aggregate function or the GROUP BY clause".
I would argue that it DOES appear in an aggregate, but since this would be futile I resorted to hard coding it...
I did this but it wouldn't work until I amended as below...
SELECT CUSTOMER,
MAX(CASE WHEN Installation Type = New
THEN Installation Date END) AS 'New',
MAX(CASE WHEN Installation Type = Existing
THEN Installation Date END) AS 'Existing'
from table
group by CUSTOMER,
(((Installation Type)))...
I did hardcode that but it looks like I'm back to square one.
Customer Number Installation Date 1 Installation Date 2 Installation Date 3
100008001 29/01/2010
100008001 12/02/2010 12/02/2010
100008001
It still returns a null row and one of the dates stays on it's own row even though...
Sorry, I forgot to mention that I have added a MAX to the dates returned.
I still have a problem though!
Because there is now a MAX in the SELECT statement, a GROUP BY clause is built.
But the INSTALLATION TYPE from the CASE WHEN statement does not appear in this GROUP BY.
I could hard code...
Thanks again, I'll have a look at this now I've got a bit more time, but since it was new to me I went with an old technique.
Long story short, I previously had this...
NAME INSTALLATION TYPE INSTALLATION DATE
Mr Jones New 03/01/2010
Mr Jones Existing 05/02/2010
Mr Jones NA NA
But I've...
I had a potentially new solution but I got a bit stuck.
My idea was to have a new object for each date, so that they all appear on one row, as below...
NAME NEW INSTALLATION DATE OTHER INSTALLATION DATE
Mr Jones 03/01/2010 05/02/2010
I used to do this in Oracle using the Max Decode...
Here's what the query returns if I don't use a MAX on anything (or if I just use it against one of the objects)...
Customer Install Date Item No. Install Type
100008001 11/01/2010 1108216 NA
100008001 29/01/2010 1173516 HouseMove
100008001 12/02/2010 1187266 Existing Customer
100008001 NA...
Thanks for this Steve.
I've tried that for the Install date, bringing back only one line as hoped, but I'll need to do the same for all of the other objects which do the same thing I suppose.
The danger is that the max returns the alpha-numerically highest value rather than the one I need...
Allow me to explain.
The customer below has both an Install Type of "Existing..." and an Install Type of "NA".
Almost all customers in the dataset have this NA.
If I filter down to only show customers with "Existing" I'll never see the genuine "NA" cases and therefore I won't get the split...
Hello there and thanks for the reply...
"Is Not Null" won't work for me I'm afraid.
I'm trying to create a summary crosstab to show the split of customers installed and not installed.
My variable therefore says "= If <Install Type - Full Desc>="New Customer Addition" Then "Installed" Else...
Hello
I'm new to the board but not new to Business Objects.
I can't, however, seem to fix this seemingly simple problem.
A date field within my universe must have multiple rows. One for each value and one to return a null value.
Therefore, when I attempt to build a variable to ask if a...
This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
By continuing to use this site, you are consenting to our use of cookies.