Logic implementation

[Anonymous]

Hello All,

I need your assistance for below requirement, I have a table like this-

ILITM	ILMCU	Transaction_Date	#Transaction_Qty	#ONHAND_QTY
72331	11091	15-09-2015	6930	9450
72331	11091	07-08-2015	8820	9450
72331	11091	23-05-2015	8800	9450
72302	11091	12-09-2015	16000	1024500
72302	11091	12-09-2015	16500	1024500
72302	11091	12-09-2015	24000	1024500
72302	11091	12-09-2015	26000	1024500

Client requirement is as below in Expected column,

ILITM	ILMCU	Transaction_Date	#Transaction_Qty	#ONHAND_QTY	Expected
72331	11091	15-09-2015	6930	9450	6930
72331	11091	07-08-2015	8820	9450	2520
72331	11091	23-05-2015	8800	9450	-6300
72302	11091	12-09-2015	16000	1024500	16000
72302	11091	12-09-2015	16500	1024500	1008500
72302	11091	12-09-2015	24000	1024500	992000
72302	11091	12-09-2015	26000	1024500	968000

In above Expected column 6930 = Transaction_Qty of last Transaction_Date of ILITM = 72331,

                      2520 = 9450-6930

                     -6300 = 2520 - 8820

Similarly 16000 = Transaction_Qty of last Transaction_Date of ILITM = 72302,

                 1008500 = 1024500 - 16000,

                  992000 =  1008500 - 16500

In above Expected table in Expected column we need only ILITM and ILMCU wise value which summation should equal to Onhand_qty for each ILITM and ILMCU.

for example in above table for ILITM and ILMCU as 72331 and 11091 we have  ONHAND_QTY = 9450 ,so In Expected column require only 6930 and 2520 because 6930+2520 = 9450

similarly for 72302 and 11091 ONHAND_QTY  = 1024500 , So In Expected column require only 16000 and 1008500 because 16000+1008500 = 1024500.

Your suggestion would be appreciated.

[Gysbert_Wassenaar]

This should do it:

T1:
LOAD *, 
  if(Previous(ILITM)=ILITM,#ONHAND_QTY-peek('CumTQ'),#Transaction_Qty ) as Expected,
  if(Previous(ILITM)=ILITM,rangesum(#Transaction_Qty,peek('CumTQ')),#Transaction_Qty) as CumTQ
INLINE [
    ILITM, ILMCU, Transaction_Date, #Transaction_Qty, #ONHAND_QTY
    72331, 11091, 15-09-2015, 6930, 9450
    72331, 11091, 07-08-2015, 8820, 9450
    72331, 11091, 23-05-2015, 8800, 9450
    72302, 11091, 12-09-2015, 16000, 1024500
    72302, 11091, 12-09-2015, 16500, 1024500
    72302, 11091, 12-09-2015, 24000, 1024500
    72302, 11091, 12-09-2015, 26000, 1024500
];

[Anonymous]

Hi Gysbert,

Thank you so much for your quick response this logic is working fine but how to apply limitation on expected column means Client want only those Transaction_Qty which summation will equal to Onhand_QTY.

as in below Image I need the data excluding highlighted rows.

[Gysbert_Wassenaar]

only those Transaction_Qty which summation will equal to Onhand_QTY

Can you explain that with some examples?

[Anonymous]

Hi Gysbert,

Here is example

    ITM,     MCU, Trans_Date , TrnsQty, Stock

    72331, 11091, 15-09-2015, 6930, 9450

    72331, 11091, 07-08-2015, 8820, 9450

    72331, 11091, 23-05-2015, 8800, 9450

in above table for same ITM and MCU we have three transaction 6930,8820 & 8800 but we have as on stock 9450,

so we want only transaction of 9450 means we will subtract stock - TrnsQty and stop subtraction when sum of       'stock - TrnsQty' = Stock

e.g  9450- 6930 = 2520, 2520-8820= -6300 then we will not consider -6300 because 6930 + 2520 = 9450

[Gysbert_Wassenaar]

So you always want only the first two rows per ITM-MCU. Well, just add a row count and then remove what you don't need.

T1:
LOAD * WHERE RowNo <=2;
LOAD *, 
  if(Previous(ILITM)=ILITM,#ONHAND_QTY-peek('CumTQ'),#Transaction_Qty ) as Expected,
  if(Previous(ILITM)=ILITM,rangesum(#Transaction_Qty,peek('CumTQ')),#Transaction_Qty) as CumTQ,
  AutoNumber(RecNo(), ILITM & '|' & ILMCU) As RowNo
INLINE [
    ILITM, ILMCU, Transaction_Date, #Transaction_Qty, #ONHAND_QTY
    72331, 11091, 15-09-2015, 6930, 9450
    72331, 11091, 07-08-2015, 8820, 9450
    72331, 11091, 23-05-2015, 8800, 9450
    72302, 11091, 12-09-2015, 16000, 1024500
    72302, 11091, 12-09-2015, 16500, 1024500
    72302, 11091, 12-09-2015, 24000, 1024500
    72302, 11091, 12-09-2015, 26000, 1024500
];

[Anonymous]

Hi Gysbert,

First of all thanks a lot for your response , there is no of rows can be increase it will depend on the stock value if stock value adjust in two rows of transaction then it should stop there otherwise it will check until the stock value would not be equal to transaction qty.