Calculate occurances of 0000000000000000 incorrectly - BO bug??

[Guest_imported]

Hi All,
I work in insurance co. and just started using BO.

Just running a routine check on our data, I use the query manager and select dimension <Crd Nbr1> and set the condition <Crd Nbr1> = '0000000000000000'. I made sure I want duplicate rows and return default # rows.

The constructed table on shows 1 row of 0000000000000000! A quick check using formula =NumberOfRows(&quot;xxx&quot returns 20K records.
The weird thing is, when I create measure (CountAll(<Crd Nbr1>)) it returns only 1!

But when I view the data its all there. Even the rows read is correct. What's wrong with my formula??

 

[blom0344]

Hello Dave H5,

You ran into the habit of BO showing only distinct representation of data at report level. This no bug , but people used to working with SQL tools are a bit surprised to find this working the way it does.
Please note that BO gives a representation of query result, not the actual dataset a query returns.
If you want 10.000 rows displayed in a table with just one distinct value be sure to create a database/universe object that makes every record unique. (ROWID or ROWNUM and the like) T. Blom
Information analist
Shimano Europe
tbl@shimano-eu.com

 

[PAULSGS]

If as you say the data really is there and you want to display it, you must click on the table to make it the active object, then from the menu, click Format->Table, then Check the box marked &quot;Avoid Duplicate Row Aggregation&quot;

 

[Guest_imported]

Thanks guys,
This is mightily annoying the living c**p out of me

Initially the table looks something like

ID Approved
---- -----------
AE19 89
AE23 69

Where approved = countall(<Crd Nbr 1>) in <ID>

It's wrong (counted by hand) because the duplicate Crd Nbr's are aggregated. So I did the format->table step and the numbers are correct, but now... it's displayed like this..

ID Approved
----- ------------
AE19 89
AE19 89
... 89 times!! It's driving me nuts!!!!.. How do I get around this problem??

Thanks
John

 

[blom0344]

Hello John,

Let me get this straight. You are retrieving a dataset consisting of <ID> and <Crd Nbr 1>. You want to count the number of Crd Nbr 1 for each ID. Now if the dataset only returns unique combinations this would do the trick:

approved = count(<Crd Nbr 1>) in <ID>

If you have a dataset with non-unique combinations then set Query options to : No duplicate rows. This will modify the SQL with a distinct clause and return the same dataset as the one with the unique combinations. T. Blom
Information analist
Shimano Europe
tbl@shimano-eu.com

 

[PAULSGS]

If you want the database to give you only one row distinct for every combination of records that are the same, do this

Click Data->Edit Data provider
The query panel pops up, in the bottom left hand corner is an options box, click this,
Check &quot;No duplicate rows&quot; , re-run query.

This has a similar effect to BO's Duplicate Row Aggregation, which you have turned off.

If you want a report list number of Approved items, you might be better served by creating a new Measure Object - &quot;Number of Approved&quot;

In Designer Module, copy& paste the ID object, rename the copy (right click to Object Properties) to &quot;Number of Approved&quot;, then enclose the definition (Select Box) with
Count(yourdefinition). Save (& export) the universe

When you next run your query, if you pick the &quot;ID&quot; object & the &quot;Number of Approved&quot; object this should generate the sql like this

Select id,count(id)
from yourtable
group by id

This will give you a report like this

ID Number of
Approved
---- -----------
AE19 89
AE23 69
AE27 41
AE41 74
etc... etc....

 

[Guest_imported]

Maybe I should rephrase my question...

Data
----

ID Crd Nbr1
-- --------
AE19 123456789
AE19 123456789
AE19 345678900
AE44 123456890
AE44 123213213

Now, when I create a table with ID, Crd Nbr1 the result was

ID Crd Nbr1
-- --------
AE19 123456789 ** Note the missing 2nd field
AE19 345678900
AE44 123456890
AE44 123213213

Which lead me to ask the first question. So I did the &quot;Avoid Duplicate Row Aggregation&quot; thing... and the table is displayed correctly again. Now, I want to find how many card# are used.. so I create a measure count= CountAll(<Crd Nbr1>) in <ID>

Now I drag the ID & count and dropped it on the report.. Now I get this

ID Count
-- -----
AE19 2
AE44 2

Which is wrong because AE19's count should be 3. SO I did the whole &quot;Avoid Duplicate Row Aggregation&quot; routine again.. but this time I get..

ID Count
-- -----
AE19 3
AE19 3
AE19 3
AE44 2
AE44 2


So how do I go about fixing this. I don't have access to the designer. Really appreciate the help!!

John