Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
The data I'm analyzing has orders coming in and invoices going out.
Any one order can have anything from zero to an infinate number of invoiceses connected to it.
Normally between 1 and 3.
In my script I would like to let QV establish the order in which the invoices were created based on the value of the invoicenumber ( asc).
E.g.
OrderNo - InvoiceNo - InvoiceOrderNo
1 - 12123413 - 1
1 - 23123123 - 2
How can I pull this off in the script, i.e. create the value for InvoiceOrderNo?
I tried something like:
autonumber(aggr(sum(InvoiceNo),OrderNo))
but that started counting from the first OrderNo instead of starting from 1 per OrderNo
Anyone has any ideas?
br
Martin
Ok, understood. First do a load ordering by OrderNo and InvoiceNo like I said above and then instead of rowno() put a function similar to the function below:
if(rowno()=1 or OrderNo = peek('OrderNo'), 1, peek('SortorderNo')+1) as SortorderNo,
If peek() doesn't work use previous(OrderNo) and previous(SortorderNo) without single quotes. The point of those function is to evaluate the previous row's values.
Regards.
Martin,
In the script you can put something like this:
Load rowno() as InvoiceOrderNo,
OrderNo,
InvoiceNo,
...
From ...
Order by OrderNo, InvoiceNo;
Regards.
Hello,
No, this does not solve my problem.
Sorry for not explaining thorough enough.
I need the counter to be reset for each order.
E.g.
OrderNo - InvoiceNo - SortorderNo
12312 - 12345 - 1
12312 - 22345 - 2
12313 - 32123 - 1
12345 - 43214 - 1
12345 - 53456 - 2
12345 - 98765 - 3
Any ideas?
br
Martin
Ok, understood. First do a load ordering by OrderNo and InvoiceNo like I said above and then instead of rowno() put a function similar to the function below:
if(rowno()=1 or OrderNo = peek('OrderNo'), 1, peek('SortorderNo')+1) as SortorderNo,
If peek() doesn't work use previous(OrderNo) and previous(SortorderNo) without single quotes. The point of those function is to evaluate the previous row's values.
Regards.