Problem with data filtering

[djaykumar]

Hi All,

Please find attachment that, I have a requirement for a particular material ,plant,storage and batch need to get purchase qty - sales qty as my current stock.

Please help me in this. Its needed urgently

Regards,

Jay

[Anonymous]

Just explain what exactly you need to sum.

What is the exact material, plant , storage and batch you need?

Set analysis will resolve your issue.

[djaykumar]

Hi Ruan,

Thanks for quick replay

Exactly I want total quantity which is subtraction of (purchase qty - sales qty)

As per excel for  example i have

Material     plant     storage    batch                purchaseqty      salesqty          availableqty

Pipe2mm   1112      SPAT      CIN_00081           1                       -                       0

Pipe2mm   1112      SPAT      CIN_00081                                      1                    0

Pipe2mm   1112      NPMA      CIN_00081          1                          -                   1

I need to get availableqty  that for a material same plant,same storage and may be same batch need to substract from (purchaseqty - Salesqty)  if for a material same plant same or different storage,  different batch purchaseqty will become availableqty.

It will match with first material and then plant and storage and batch. But for some materials Storage and batch is not there in that case need to consider plant wise.

Please help me in getting this

Thanks,

Jay

[Anonymous]

I am a bit confused with what you mean,

However, already you need to below, just to enable calculations to be made.  This will ensure there are no null values.

if(IsNull([Purchase Qty]),0,1) - if(IsNull([Sale Qty]),0,1) as AvailableQty

Add that in the script then we can work through additional logic.

If you add sum(AvailableQty) into a table you will get something like the below:

[djaykumar]

Hi Ruan,

For a material pipe2mm plant 1112 storage SPAT and Batch CIN_00081  there are two line items are there.

In that one is purchase qty and another one is Saleqty both are 1  and my available qty is it should show 0.

That means Pipe2mm in 1112 plant SPAT storage location Batch CIN_00081 it should show 0.

I have tested with

if(IsNull([Purchase Qty]),0,1) - if(IsNull([Sale Qty]),0,1) as AvailableQty

And I am getting

Material_MATNR.MSEG	Plant_WERKS.MSEG	Stor. Loc._LGORT	Batch_CHARG.MSEG	Purchase Qty	Sale Qty	AvailableQty
13H05-111A4	1112	SPAT	CIN_00081	1		1
13H05-111A4	1132	SPAT	CIN_13055	1		1
13H05-111A4	1112			1		1
13H05-111A4	1132	SPAT	CIN_13055		1	-1
13H05-111A4	1112	SPAT	0000076900	1		1
13H05-111A4	1112	NPMA	CIN_00081	1		1
13H05-111A4	1112	SPAT	CIN_00081		1	-1
13H05-111A4	1112	SPAT	0000076900		1	-1
13H05-111A4	1112	SPMA	0000076900	1		1

But my required  availableqty is

needed field
0
0
1
0
0
1
0
0
1
0
1
0

That is, for a Batch no (Purchaseqty - sales qty ) = Availableqty

Help me

Thanks

Jay

[djaykumar]

Hi,

I am struck with this.

Please help me.

Thanks,

Jay