Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Reporting duplicate data

All,

I have a scenario like this:

Two tables - Positions and BookRef

Position table

==========

BookQty
B1600
B2300

BookRef table

==========

BookDescription
B1Test Book 1
B2Test Book 2
B2Test 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:

BookDescriptionQty
B1Test Book 1600
B2Test Book 2300
B2Test Book Two300

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

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

BookDescriptionQty
B1Test Book 1600

Duplicate Book Exception Report

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

BookQtyDescription
B2300
Test Book 2
Test Book Two

Is there anyway to do this?

6 Replies
Gysbert_Wassenaar

Sort of. See attached example.


talk is cheap, supply exceeds demand
Not applicable
Author

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.


talk is cheap, supply exceeds demand
Not applicable
Author

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
Partner - Specialist II
Partner - Specialist II

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

Not applicable
Author

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