Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

djaykumar
New Contributor

Problem with data filtering

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

5 Replies
qlikmeplease
Contributor III

Re: Problem with data filtering

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
New Contributor

Re: Problem with data filtering

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

Highlighted
qlikmeplease
Contributor III

Re: Problem with data filtering

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:

test.PNG

djaykumar
New Contributor

Re: Problem with data filtering

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.MSEGPlant_WERKS.MSEGStor. Loc._LGORTBatch_CHARG.MSEGPurchase QtySale QtyAvailableQty
13H05-111A41112SPATCIN_000811 1
13H05-111A41132SPATCIN_130551 1
13H05-111A41112 1 1
13H05-111A41132SPATCIN_13055 1-1
13H05-111A41112SPAT00000769001 1
13H05-111A41112NPMACIN_000811 1
13H05-111A41112SPATCIN_00081 1-1
13H05-111A41112SPAT0000076900 1-1
13H05-111A41112SPMA00000769001 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
New Contributor

Re: Problem with data filtering

Hi,

I am struck with this.

Please help me.

Thanks,

Jay