topic Re: Re: Using Loop for multiple Load statement in QlikView

Using Loop for multiple Load statement

Mon, 06 Jan 2014 06:48:57 GMT

Hello,

Kindly help me in solving the below issue.

Test:

Plant Material Tot_Qty

ABC 1001 500

XYZ 1002 1000

Order

OrderNo Plant Material Date Qty_2

1 ABC 1001 1/1/2013 100

2 ABC 1001 2/10/2013 200

3 ABC 1001 15/12/2013 200

From the above data, I need to check that for Particular Plant and Material, how many orders are placed as per order date.

For Eg,

OrderNo : 1, Qty_2 : 100.

Subtract Qty_2 from Tot_Qty and loop it till the Tot_Qty becomes 0.

Thanks

Sumeet

Re: Using Loop for multiple Load statement

Nagaraju_KCS — Mon, 06 Jan 2014 07:03:45 GMT

Hi Community,

I have same problem like this

please give me anyone solution ASAP,

---Raju

Re: Using Loop for multiple Load statement

jagan — Mon, 06 Jan 2014 07:33:17 GMT

Hi,

Can you clarify what should be your final output with example.

Regards,

Jagan.

Re: Using Loop for multiple Load statement

vikasmahajan — Mon, 06 Jan 2014 07:37:33 GMT

I think you should try RangeSum(Above(Sum(Qty), 0, RowNo()))

Please find attachment of demo which can help you.

Vikas

Re: Using Loop for multiple Load statement

Nagaraju_KCS — Mon, 06 Jan 2014 08:59:03 GMT

Hi Jagan,

Output would be

Test:

Plant Material Tot_Qty

ABC 1001 500

XYZ 1002 1000

Order

OrderNo Plant Material Date Qty_2

1 ABC 1001 1/1/2013 100

2 ABC 1001 2/10/2013 200

3 ABC 1001 15/12/2013 200

O/P:

Row1:

OrderNo Plant Material Date Qty(Tot_Qty-Qty_2)

1 ABC 1001 1/1/2013 400

Suppose Tot_Qty=500

Qty_2=100

(Tot_Qty-Qty_2)= 500-100

Now, 400 would be Tot_Qty for next OrderNo

400-200

---Raj

Re: Using Loop for multiple Load statement

jagan — Mon, 06 Jan 2014 09:09:28 GMT

Hi,

Please try this script

Temp:

LOAD * Inline [

Plant, Material, Tot_Qty

ABC, 1001, 500

XYZ, 1002, 1000];

Left Join

LOAD *

Inline [

OrderNo, Plant, Material, Date, Qty_2

1, ABC, 1001, 1/1/2013, 100

2, ABC, 1001, 2/10/2013, 200

3, ABC, 1001, 15/12/2013, 200

];

Data:

LOAD

If(Previous(Plant) = Plant and Previous(Material) = Material , Peek('TempTotal') - Qty_2, Tot_Qty - Qty_2) AS TempTotal

RESIDENT Temp

ORDER BY Plant, Material, OrderNo;

DROP TABLE Temp;

Regards,

Jagan.

Re: Re: Using Loop for multiple Load statement

Nagaraju_KCS — Mon, 06 Jan 2014 11:22:39 GMT

Hi Jagan,

The above Solution is not working for some values.

PFB snapshot of the data.

Order Date	Material No	Plant	ATP Qty	Ordered	Cumulative Qty	Status
2/11/2013	28112	A016	55050	100	54950	OK
22-11-2013	28112	A016	55050	100	54850	OK
26-11-2013	28112	A016	55050	100	54750	OK
26-11-2013	28112	A016	55050	200	54550	OK

Order Date	Material No	Plant	ATP Qty	Ordered	Cumulative Qty	Status
2/11/2013	28112	A016	100	80	20	OK
22-11-2013	28112	A016	100	50	-30	Invalid

The solution provided by you is giving the attached output.

Re: Using Loop for multiple Load statement

maxgro — Mon, 06 Jan 2014 22:43:16 GMT

please see attachment, result in image

Regards

Re: Re: Using Loop for multiple Load statement

jagan — Tue, 07 Jan 2014 02:09:59 GMT

Hi,

Can you attach the file and data which you are working, I think your script has issue. Did you ordered the resultset

ORDER BY Plant, Material, OrderNo

Regards,

Jagan.

Re: Using Loop for multiple Load statement

Tue, 07 Jan 2014 04:22:06 GMT

Hi Massimo/Jagan,

The Result set for the data should be :

Order Date	Material No	Plant	ATP Qty	Ordered	CumulativeQty	Status
2/11/2013	28112	A016	55050	100	54950	OK
22-11-2013	28112	A016	55050	100	54850	OK
26-11-2013	28112	A016	55050	100	54750	OK
26-11-2013	28112	A016	55050	200	54550	OK

For 1st Row, ATP Qty - Ordered (55050-100 = 54950)

For 2ns Row, ATP Qty should be the result of 1st Row. ie. (54950-100 = 54850) and so on.

Kinldy help as how to get these type of result in a loop. Also the moment we get any negative result set, loop should be terminated with the apprpriate status.

Regards

Sumeet