topic Pivot table not returning proper data in QlikView

Pivot table not returning proper data

Anonymous — Tue, 24 Nov 2015 17:20:47 GMT

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.

Re: Pivot table not returning proper data

settu_periasamy — Tue, 24 Nov 2015 17:24:11 GMT

Did you try Only() function?

Re: Pivot table not returning proper data

rwunderlich — Tue, 24 Nov 2015 17:34:56 GMT

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

Re: Pivot table not returning proper data

Anonymous — Tue, 24 Nov 2015 20:03:27 GMT

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.

Re: Pivot table not returning proper data

MarcoWedel — Tue, 24 Nov 2015 23:07:02 GMT

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

Re: Pivot table not returning proper data

Anonymous — Wed, 25 Nov 2015 15:47:43 GMT

Thanks!