Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
Sort of. See attached example.
Gysbert, my personal QV license doesn't allow to open your qvw. Would youy mind just pasting the code snippet?
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.
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.
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
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