topic Re: Create Invoice Line Number in QlikView

Create Invoice Line Number

keithlawrence — Fri, 17 Feb 2017 15:47:14 GMT

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

Re: Create Invoice Line Number

sunny_talwar — Fri, 17 Feb 2017 15:59:53 GMT

May be this

LOAD Invoice,

Product,

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

FROM ...

Re: Create Invoice Line Number

vishsaggi — Fri, 17 Feb 2017 16:01:55 GMT

Try this?

Tab1:

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

Invoice, Product

11111, A

11111, B

11111, C

11112, A

11112, C

11113, A

];

Re: Create Invoice Line Number

keithlawrence — Fri, 17 Feb 2017 16:36:09 GMT

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

Re: Create Invoice Line Number

sunny_talwar — Fri, 17 Feb 2017 16:43:22 GMT

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

Tab1:

LOAD * INLINE [

Invoice, Product

11111, A

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;

Re: Create Invoice Line Number

keithlawrence — Mon, 20 Feb 2017 11:33:16 GMT

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

Re: Create Invoice Line Number

sunny_talwar — Mon, 20 Feb 2017 16:22:31 GMT

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