topic Re: Getting Line wise different with Total and remaining Difference in QlikView

Getting Line wise different with Total and remaining Difference

brijeshvma — Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

I need urgent help from scripting side

CustomerId	Delivery Date	Order Qty	Total Order Qty	Total Delivered Qty	Delivered Line Wise Item Qty	Pending Qty
1001	20-09-14	4	904	753	4	0
1001	15-10-15	200	904	753	200	0
1001	15-11-15	200	904	753	200	0
1001	15-12-15	200	904	753	200	0
1001	15-01-16	200	904	753	149	51
1001	16-01-16	100	904	753	0	100

I have data something like above first five table and and want output for last 2 column..

its like i have Totao order quantity and 4th column and line wise order qty .. and as well i have against the order how much total i have

delivered ( 904 - 753 = Remaining Pending qty 151) ... but i want to show data like last 2 column . it means date wise first first order of 4 qty fulfil then second order qty and so on .. but once its reached the total of total delivered qty its show in Pending qty column

Kindly help me to resolve this..

As well I am attaching a sample excel file with required data in Yellow column.

Re: Getting Line wise different with Total and remaining Difference

brijeshvma — Wed, 10 May 2017 18:21:37 GMT

Can any one give me idea to resolve it

Re: Getting Line wise different with Total and remaining Difference

Anonymous — Wed, 10 May 2017 19:55:05 GMT

could you change the yellow columns into expression in you excel file please...

Re: Getting Line wise different with Total and remaining Difference

brijeshvma — Thu, 11 May 2017 05:48:01 GMT

Hi Robin,

Which expression ?

As i created dummy data .. what client looking for .. I currently need logic to make the data as yellow column.

Re: Getting Line wise different with Total and remaining Difference

eliran — Thu, 11 May 2017 06:29:44 GMT

Hi Brijesh,

You're asking the community to help you find out the logic behind your customer's excel spread sheet.

I suggest you ask *him* that question, we can help you with calculations and methods to work it out afterwards.

BR,

Eliran.

Re: Getting Line wise different with Total and remaining Difference

brijeshvma — Thu, 11 May 2017 06:50:10 GMT

Hi Eliran ,

What i understand with Requirement ... let me try to explain.. Say for Customer 1001

Delivery Date	Order Qty	Total Order Qty	Total Delivered Qty	Delivered Line Wise Item Qty	Pending Qty
20-09-14	4	904	753	4	0
15-10-15	200	904	753	200	0
15-11-15	200	904	753	200	0
15-12-15	200	904	753	200	0
15-01-16	200	904	753	149	51
16-01-16	100	904	753	0	100

We have a Line wise order Qty : 4, 200,200,200,200,100 ( Total of This is 904) .. And i have Against the order 904 .. total delivered qty i.e 753 . Means my pending qty is 151 to fulfill 904 order.

So,

Order Qty Total Delivered Qty Exp Delivered Line Wise Item Qty Pending Qty

4 753 ( 4 < 753 ) (753 - 4 = 749 ) 4 0

200 753 ( 200< 749 ) (749 - 200 =549) 200 0 ( For 200 Order QTy)

200 753 (200<549) ( 549 -200 = 349) 200 0 ( for 200 order qty)

200 753 (200<349)(349-200 = 149 ) 200 0 ( for 200 qty )

200 753 (200 > 149 ) ( it should consider 0) 149 51 ( 200-149 =51 Pending qty)

100 753 (100>0 ) ( it should consider 0) 0 100 ( Pending entire 100 qty)

Re: Getting Line wise different with Total and remaining Difference

Anonymous — Fri, 12 May 2017 07:21:09 GMT

sorry, but for we can not know how much ist delivered per line (Date , CustomerID) ...

I think the community is the wrong place for clarifying the customer needs... that is your task

We can assist you realizing your clearly defined needs in QlikView.