Null values causing multiple rows

[jonnythesaint]

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 specific customer has this date or not, that customer returns both a yes and a no, therefore being incorrectly double counted when I apply a sum.

The customer should only be returned as a "yes" if the date exists.

How do I get this on one row please?

Thanks

Jonny

 

[skrandel]

I must be missing something.

Can't you write the query so that only the dates are "returned" and the null is ignored. "where table.date is not null"

Steve Krandel
Intuit

 

[jonnythesaint]

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 "Not Installed""

Since each customer will always have a "null" row aswell as the "install date", he will appear in both columns and get counted twice.

I just want him to appear in the "Installed" column, but because he has two rows he appears in both.

Does this make sense?

 

[jonnythesaint]

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 between the two.

I need the customer below to show ONE row for Install Type - Existing.

Thanks again..

Cust.No. Install Type - Full Desc
1008001 Existing Customer Service Addition
1008001 NA

 

[skrandel]

So, when you write the query, just bring back the min or max of install type. Then you will get only 1 per customer.

Steve Krandel
Intuit

 

[jonnythesaint]

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. I'll do some more digging!

Cheers...

 

[jonnythesaint]

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


Here's what it returns if I put a MAX on all of the objects (I'd need to do this to avoid the above happening).

Customer Install Date Item No. Install Type
100008001 12/02/2010 1187266 NA


Notice how it's returning the incorrect Install Type of NA against the Date/Item No.?

Any ideas?

Cheers

 

[blom0344]

This is basic SQL behavior of an aggregate. The set returned is aggregated against the non-aggregate (here: customer) and the individual max values for each other field are returned.
In other words a regular aggregate will not return 1 entire row from a set, but a composite set based on individual columns.

If you want 1 row returned for each customer where ALL columns are from that record you can do the following:

Scenario 1:

Add a derived table to the universe, like:

SELECT CUSTOMER, MAX(INSTALL_DATE) FROM WHATEVERTABLE
GROUP BY CUSTOMER

and join this on both customer and MAX(INSTALL_DATE) to resp. customer and install date in the existing table through an inner join. Fetching any object from the derived table will automatically result in restrincting the set to 1 row per customer with latest install date

Scenario 2:

Replace the existing customer dimension table with a view or derived table that stores an additional object that ranks according to install date for each customer. These are known as SQL OLAP functions and make it much easier to select the latest (or earliest) entry for a certain dimension. This works well for relative small dimension tables, not for large facts!!

Ties Blom

 

[jonnythesaint]

Thanks very much for the reply...

I'll start looking at this asap.

Cheers

 

[jonnythesaint]

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 function, but I guess I'll need to use CASE WHEN now.

The trouble is that I'll be asking it to look for a TYPE (character) but return a DATE (date).

Will this work please?

 

[jonnythesaint]

This worked!

Thanks for all your input.

 

[blom0344]

That's nice. No idea what you wrote using CASE WHEN, but from the original request a rank would seem pretty elegant.

Consider:

1008001 Existing Customer Service Addition
1008001 NA
1008002 NA
1008003 Existing Customer Service Addition

SELECT CUSTOMER,RANK() OVER (PARTITION BY CUSTOMER ORDER BY TYPE ASC) AS #RANK,TYPE FROM TABLE

yields:

1008001 1 Existing Customer Service Addition
1008001 2 NA
1008002 1 NA
1008003 1 Existing Customer Service Addition

You then only have to filter on #rank = 1 to fetch 1 row for each customer.
If 'Existing Customer Service Addition' exists as entry, then this will be returned, otherwise 'NA' will

Ties Blom

 

[jonnythesaint]

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 converted to this...

NAME NEW INSTALLATION DATE OTHER INSTALLATION DATE
Mr Jones 03/01/2010 05/02/2010


By creating the following objects...

CASE WHEN Installation Type = New
THEN Installation Date

CASE WHEN Installation Type = Existing
THEN Installation Date

 

[blom0344]

Only applying a 'case when' this way should still return 2 rows. However with a max against them it will roll up to 1 row for each customer.
This , however will not scale I guess. Are you sure it returns what you need in all circumstances. I would check thoroughly..

Ties Blom

 

[jonnythesaint]

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, but is there a way of doing this via the Universe?

Thanks again, I really appreciate your time...

 

[jonnythesaint]

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 the other two worked!

 

[blom0344]

I would expect:

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

Ties Blom

 

[jonnythesaint]

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)))

Even then though, I ended up with the problem above.

I'll have a look through your original post, thanks, but I may struggle a little as some of the concepts seemed new.

 

[blom0344]

OLAP functions are available from Oracle 9, DB2 7 and SQL server 2005 onwards. I guess you are on Oracle.

In the 'old' days there used to be configuration files (.prm ?)on the client that could influence SQL generation.

Check OLAP / analytical functions:

http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:1212501913138

Ties Blom

 

[jonnythesaint]

No I'm on SQL Server.

I'm better with Oracle and never had any problems with Decodes on there, but this is all new to me!