Reporting duplicate data

[#1]

All,

I have a scenario like this:

Two tables - Positions and BookRef

Position table

==========

Book	Qty
B1	600
B2	300

BookRef table

==========

Book	Description
B1	Test Book 1
B2	Test Book 2
B2	Test Book Two

As you can see, if I join the two tables, I will have the B2 as duplicate as the refrence data has a duplicate. Like this:

Book	Description	Qty
B1	Test Book 1	600
B2	Test Book 2	300
B2	Test Book Two	300

What I need to do instead is, show two reports -- one showing the non-duplicate data and other JUST the duplicate data as an exception. Something like this:

Standard Report

============

Book	Description	Qty
B1	Test Book 1	600

Duplicate Book Exception Report

========================

Book	Qty	Description
B2	300	Test Book 2
		Test Book Two

Is there anyway to do this?

[Gysbert_Wassenaar]

Sort of. See attached example.

[#3]

Gysbert, my personal QV license doesn't allow to open your qvw. Would youy mind just pasting the code snippet?

[Gysbert_Wassenaar]

Load some data:

T1:

LOAD * INLINE [

    Book, Qty

    B1, 600

    B2, 300

];

T2:

LOAD * INLINE [

    Book, Description

    B1, Test Book 1

    B2, Test Book 2

    B2, Test Book Two

];

Create a pivot table with three dimensions:

1. Book

2. Qty

3. Description

Use something like sum({<Book={'=count(Book)>1'}>}Qty) as expression. Click on the + in front of the expression and click on Text Color. In the definition field enter red(0) to make the text color transparant, i.e. invisible. Use a space as expression label. Finally resize the expression column as much as possible. Use a macro if you want to hide the column completely. See this post.

[#5]

I think you stressed more on the display format. My main issue was that Ineed to show ONLY the duplicate rows. With what you gave, I end up showing as follows:

Book,Qty,Description

B1, 600, Test Book 1

B2, 300, Test Book 2

              Test Book Two

However, what I need is splitting this in two Pivots -- one showing just the non-duplicated row (B1,600,Test Book 1)

and other the duplicated rows.

[shree909]

Hi

What  Gysbert Wassenaar said shows the  duplicate records

if you want to create 2 separate pivot tables onw with duplicate and non duplicate,

use the same dimesion  and change the expression

for duplicates  use this   =sum({<Book={'=count(Book)>1'}>}Qty)

for non dupllcates use this    =sum({<Book={'=count(Book)=1'}>}Qty)

Hope this helps

[#7]

Using =sum({<Book={'=count(Book)>1'}>}Qty) shows both duplicate and non-duplicate

It works if I do =sum({<Book={'=count(Description)>1'}>}Qty)

Thanks people