topic Re: Problem with data filtering in QlikView

Problem with data filtering

djaykumar — Fri, 12 Oct 2018 06:09:52 GMT

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

Re: Problem with data filtering

Anonymous — Fri, 12 Oct 2018 06:55:29 GMT

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.

Re: Problem with data filtering

djaykumar — Fri, 12 Oct 2018 07:17:07 GMT

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

Re: Problem with data filtering

Anonymous — Fri, 12 Oct 2018 07:39:09 GMT

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:

Re: Problem with data filtering

djaykumar — Fri, 12 Oct 2018 08:50:42 GMT

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

Re: Problem with data filtering

djaykumar — Mon, 15 Oct 2018 03:26:09 GMT

Hi,

I am struck with this.

Please help me.

Thanks,

Jay