Pivot table not returning proper data

[Anonymous]

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.

[settu_periasamy]

Did you try Only() function?

[rwunderlich · Accepted solution]

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]

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]

Thanks!