Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
May be this
LOAD Invoice,
Product,
AutoNumber(RowNo(), Invoice) as [Invoice Line]
FROM ...
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
];
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
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;
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
Order by only works on Resident loads, you cannot use do Order By when loading from QVD or Excel or any flat file.