topic Re: Reporting duplicate data in QlikView

Reporting duplicate data

Thu, 13 Jun 2013 14:08:48 GMT

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
B2	300	Test Book Two

Is there anyway to do this?

Re: Reporting duplicate data

Gysbert_Wassenaar — Thu, 13 Jun 2013 14:29:42 GMT

Sort of. See attached example.

Re: Reporting duplicate data

Thu, 13 Jun 2013 14:32:50 GMT

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

Re: Reporting duplicate data

Gysbert_Wassenaar — Thu, 13 Jun 2013 14:38:24 GMT

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.

Re: Reporting duplicate data

Thu, 13 Jun 2013 15:20:17 GMT

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.

Re: Reporting duplicate data

shree909 — Thu, 13 Jun 2013 15:43:03 GMT

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

Re: Reporting duplicate data

Thu, 13 Jun 2013 16:28:42 GMT

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