Create Invoice Line Number

[keithlawrence]

Hi all,

I have a list of invoice numbers and various invoice lines but my invoice line number field is blank. Is there a way to create an invoice line number in the script? I can do it in Excel easily but I want to automate the process.

e.g

Invoice     Product      Invoice Line

11111        A               1

11111        B               2

11111        B               3

11111        C               4

11112          A               1

11112          C               2

11113          A               1

Thanks

Keith

[sunny_talwar]

May be this

LOAD Invoice,

     Product,

     AutoNumber(RowNo(), Invoice) as [Invoice Line]

FROM ...

[vishsaggi]

Try this?

Tab1:

LOAD *, IF(Invoice = Previous(Invoice), Peek(InvoiceLine) + 1, 1) AS InvoiceLine INLINE [

Invoice,     Product

11111,        A              

11111,        B              

11111,        B              

11111,        C              

11112,          A            

11112,          C            

11113,          A            

];

[keithlawrence]

Hi Sunny,

This worked but took 20 mins to process 230,000 rows which may become unworkable when I roll this out to millions of rows.

Thanks

Keith

[sunny_talwar · Accepted solution]

You can try vishsaggi‌'s method with a slight variation

Tab1:

LOAD * INLINE [

Invoice,    Product

11111,        A             

11111,        B             

11111,        B             

11111,        C             

11112,          A           

11112,          C           

11113,          A           

];

FinalTable:

LOAD *,

  If(Invoice = Previous(Invoice), RangeSum(Peek(InvoiceLine), 1), 1) AS InvoiceLine

Resident Tab1

Order By Invoice;

DROP Table Tab1;

[keithlawrence]

Thanks Sunny.

That worked. Had to remove the Order By statement as I was getting a garbage error when loading from a QVD for some reason. My invoices are already ordered by Invoice so I should be good

[sunny_talwar]

Order by only works on Resident loads, you cannot use do Order By when loading from QVD or Excel or any flat file.