topic Compare data from the same field in the script in QlikView

Compare data from the same field in the script

Mon, 12 Apr 2010 12:23:56 GMT

Due to some less than perfect management of our ERP system, I need to edit data loaded from the database in the script (it regards the received quantity on purchase orders).

When there have been several deliveries and thus several records made for the same order line, the registered quantity received gets messed up (in the database). However for every quantity registered there's a field where the quantity already invoiced is registered. By using the quantity invoiced for one data entry I can calculate the quantity actually received for another data entry.

In other words I need to compare values from the same field.

How can I do this in the script?

I need to compare all data for each order line and calculate new value and connect them to the right delivery.

This is the kind of logic I would need to implement (for every order line):

Load
OrderLineID

For i = 1 to count(QtyInvoiced)-1
if(Max(QtyReceived, i) > Min(QtyAlreadyInvoiced, i),
Min(QtyAlreadyInvoiced, i+1) - Min(QtyAlreadyInvoiced, i)) as QtyActualReceived,
OrderLineID + QtyInvoiced as OrderLineIDNew,
Resident OrderDetails
group by OrderLineID

Is it possible to do this in the script somehow?

Compare data from the same field in the script

d_pranskus — Mon, 12 Apr 2010 13:18:38 GMT

Could you attach sample application with demo data attached to a post?

Darius

Compare data from the same field in the script

Mon, 12 Apr 2010 14:12:29 GMT

Here's some sample data:

OrderDetails:
LOAD * INLINE [
OrderLineID, QtyInvoiced, QtyRecieved
34879 10, 0, 19856
34879 10, 2354, 17502
34879 10, 14044, 5812
40710 10, 0, 20986
40710 10, 11012, 9911
40710 10, 20923, 63
];

I want to implement the following logic on this data:

I want a resulting table with the following data:

OrderDetailsActual:
LOAD * INLINE [
OrderLineID, QtyInvoiced, QtyRecieved, QtyActualRecieved
34879 10, 0, 19856, 2354
34879 10, 2354, 17502, 11690
34879 10, 14044, 5812, 5812
40710 10, 0, 20986, 11012
40710 10, 11012, 9911, 9911
40710 10, 20923, 63, 63
];

Compare data from the same field in the script

d_pranskus — Mon, 12 Apr 2010 14:27:22 GMT

Could you explain BusinessLogic for this algorithm or business logic related to the data you provided:

I need this because I don't have field QtyAlreadyInvoiced, And it would be easier for me to understand the logic.

Cheers

Darius

Compare data from the same field in the script

Mon, 12 Apr 2010 14:39:07 GMT

Sorry, I made mistake, there's no QtyAlreadyInvoiced (they're all supposed to be QtyInvoiced

so it would look like following:

For i = 1 to count(QtyInvoiced)-1
if(Max(QtyReceived, i) > Min(QtyInvoiced, i),
Min(QtyInvoiced, i+1) - Min(QtyInvoiced, i)) as QtyActualReceived,
OrderLineID + QtyInvoiced as OrderLineIDNew,

Thanks!

Compare data from the same field in the script

d_pranskus — Mon, 12 Apr 2010 14:56:49 GMT

Please find solution attached

Cheers

Darius

Compare data from the same field in the script

Mon, 12 Apr 2010 15:33:17 GMT

Thank you very much for all your help!!!

It seems to work great now!

Thanks,
Rickard

Compare data from the same field in the script

d_pranskus — Mon, 12 Apr 2010 15:40:33 GMT

If so, could you confirm the answer, please, to close the issue. Thanks.

Cheers,

Darius