Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Pivot table not returning proper data

Hi All,

I've run into a situation that I can't seem to fix.  Our invoices often have the exact same information on multiple rows. Same item, description, qty, unit price, etc.  It doesn't seem to matter how I try and slice the data, I can't ever seem to find that second row.  Is there some function that would eliminate this issue?  I've tried distinct, however because they are both the same quantity, price, etc that doesn't seem to help.

1 Solution

Accepted Solutions
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

The same combination of dimension values will create only one row in the chart, regardless of how many rows share those dimensions. To make these duplicate lines appear as separate lines, you must add a dimension that contains unique values, like InvoiceLineNo. If such a field does not actually exist in the data, you can generate a unique key in the load using RecNo().

-Rob

View solution in original post

5 Replies
settu_periasamy
Master III
Master III

Did you try Only() function?

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

The same combination of dimension values will create only one row in the chart, regardless of how many rows share those dimensions. To make these duplicate lines appear as separate lines, you must add a dimension that contains unique values, like InvoiceLineNo. If such a field does not actually exist in the data, you can generate a unique key in the load using RecNo().

-Rob

Anonymous
Not applicable
Author

There isn't any unique fields in this instance.  I had tried to concatenate fields thinking that there might be something there that would make this return as unique, however these two lines are exact duplicates in this instance.  If there is a InvoiceLineNo in the tables, I can't find any reference to it.

I'm not familiar with RecNo, I'll go digging for more information on that.

Thanks!

Edit:  There is a LNITMSEQ field in GP, but reading around it appears there might be some limitations/problems of that field.  Still need to learn how to apply RecNo.

MarcoWedel

Hi,

one example:

Instead of loading:

tabInvoices:

LOAD InvoiceID,

          item,

          description,

          qty,

          [unit price]

From YourSource;

try with:

tabInvoices:

LOAD InvoiceID,

          Autonumber(RecNo(), InvoiceID) as InvoiceLine,

          item,

          description,

          qty,

          [unit price]

From YourSource;

to get distinct line numbers (1 to number of lines per invoice) using RecNo() like Rob suggested.

hope this helps

regards

Marco

Anonymous
Not applicable
Author

Thanks!