Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

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?

Tags (2)
6 Replies

Re: Reporting duplicate data

Sort of. See attached example.


talk is cheap, supply exceeds demand
Not applicable

Re: Reporting duplicate data

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

Re: Reporting duplicate data

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

Re: Reporting duplicate data

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
Valued Contributor II

Re: Reporting duplicate data

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

Re: Reporting duplicate data

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

Community Browser