Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Logic implementation

Hello All,

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

     

ILITMILMCUTransaction_Date#Transaction_Qty#ONHAND_QTY
723311109115-09-201569309450
723311109107-08-201588209450
723311109123-05-201588009450
723021109112-09-2015160001024500
723021109112-09-2015165001024500
723021109112-09-2015240001024500
723021109112-09-2015260001024500

Client requirement is as below in Expected column,

    

ILITMILMCUTransaction_Date#Transaction_Qty#ONHAND_QTYExpected
723311109115-09-2015693094506930
723311109107-08-2015882094502520
723311109123-05-201588009450-6300
723021109112-09-201516000102450016000
723021109112-09-20151650010245001008500
723021109112-09-2015240001024500992000
723021109112-09-2015260001024500968000

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.

6 Replies
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

];


talk is cheap, supply exceeds demand
Anonymous
Not applicable
Author

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.

Capture.PNG

Gysbert_Wassenaar

only those Transaction_Qty which summation will equal to Onhand_QTY

Can you explain that with some examples?


talk is cheap, supply exceeds demand
Anonymous
Not applicable
Author

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

];


talk is cheap, supply exceeds demand
Anonymous
Not applicable
Author

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.