Use dates in data to create filter

[markgraham123]

Hi all,

I have available stock of 16 for Item 'A'.

But req. is 26.

Now i want to allocate that 16 to the oldest deadline IDs.

In this case, i want to allocate available 16 to the IDs till deadline 7/22 as they are close by- since it counts to 15.

ID	Deadline	ITEM	Req.	Avail.
1	7/10/2017	A	1.00	16.00
2	7/12/2017	A	1.00	16.00
3	7/15/2017	A	1.00	16.00
4	7/15/2017	A	1.00	16.00
5	7/15/2017	A	1.00	16.00
6	7/18/2017	A	1.00	16.00
7	7/18/2017	A	1.00	16.00
8	7/18/2017	A	1.00	16.00
9	7/19/2017	A	1.00	16.00
10	7/19/2017	A	1.00	16.00
11	7/20/2017	A	1.00	16.00
12	7/21/2017	A	1.00	16.00
13	7/21/2017	A	1.00	16.00
14	7/21/2017	A	1.00	16.00
15	7/22/2017	A	1.00	16.00
16	7/24/2017	A	2.00	16.00
17	7/24/2017	A	2.00	16.00
18	7/24/2017	A	1.00	16.00
19	7/24/2017	A	1.00	16.00
20	7/25/2017	A	2.00	16.00
21	7/25/2017	A	1.00	16.00
22	7/25/2017	A	1.00	16.00
23	7/26/2017	A	2.00	16.00
24	7/28/2017	A	1.00	16.00
25	7/29/2017	A	1.00	16.00
26	8/2/2017	A	1.00	16.00

Any help is highly appreciated!!

Please see the attachment

[sunny_talwar]

How would the desired output look like from the data provided?

[Sergey_Shuklin]

Hello, Mark!

Like this?

[markgraham123]

Here it is:

1	7/10/2017	A	16.00	1.00	Y
2	7/12/2017	A	16.00	1.00	Y
3	7/15/2017	A	16.00	1.00	Y
4	7/15/2017	A	16.00	1.00	Y
5	7/15/2017	A	16.00	1.00	Y
6	7/18/2017	A	16.00	1.00	Y
7	7/18/2017	A	16.00	1.00	Y
8	7/18/2017	A	16.00	1.00	Y
9	7/19/2017	A	16.00	1.00	Y
10	7/19/2017	A	16.00	1.00	Y
11	7/20/2017	A	16.00	1.00	Y
12	7/21/2017	A	16.00	1.00	Y
13	7/21/2017	A	16.00	1.00	Y
14	7/21/2017	A	16.00	1.00	Y
15	7/22/2017	A	16.00	1.00	Y
16	7/24/2017	A	16.00	2.00
17	7/24/2017	A	16.00	2.00
18	7/24/2017	A	16.00	1.00
19	7/24/2017	A	16.00	1.00
20	7/25/2017	A	16.00	2.00
21	7/25/2017	A	16.00	1.00
22	7/25/2017	A	16.00	1.00
23	7/26/2017	A	16.00	2.00
24	7/28/2017	A	16.00	1.00
25	7/29/2017	A	16.00	1.00
26	8/2/2017	A	16.00	1.00

[sunny_talwar]

Try this

Test:

LOAD * Inline [

ID, Deadline, ITEM, Req., Avail.

1, 7/10/2017, A, 1.00, 16.00

2, 7/12/2017, A, 1.00, 16.00

3, 7/15/2017, A, 1.00, 16.00

4, 7/15/2017, A, 1.00, 16.00

5, 7/15/2017, A, 1.00, 16.00

6, 7/18/2017, A, 1.00, 16.00

7, 7/18/2017, A, 1.00, 16.00

8, 7/18/2017, A, 1.00, 16.00

9, 7/19/2017, A, 1.00, 16.00

10, 7/19/2017, A, 1.00, 16.00

11, 7/20/2017, A, 1.00, 16.00

12, 7/21/2017, A, 1.00, 16.00

13, 7/21/2017, A, 1.00, 16.00

14, 7/21/2017, A, 1.00, 16.00

15, 7/22/2017, A, 1.00, 16.00

16, 7/24/2017, A, 2.00, 16.00

17, 7/24/2017, A, 2.00, 16.00

18, 7/24/2017, A, 1.00, 16.00

19, 7/24/2017, A, 1.00, 16.00

20, 7/25/2017, A, 2.00, 16.00

21, 7/25/2017, A, 1.00, 16.00

22, 7/25/2017, A, 1.00, 16.00

23, 7/26/2017, A, 2.00, 16.00

24, 7/28/2017, A, 1.00, 16.00

25, 7/29/2017, A, 1.00, 16.00

26, 8/2/2017, A, 1.00, 16.00

];

FinalTable:

LOAD *,

  If(ITEM = Previous(ITEM), RangeSum(Peek('AccReq'), [Req.]), [Req.]) as AccReq,

  If(Avail. - If(ITEM = Previous(ITEM), RangeSum(Peek('AccReq'), [Req.]), [Req.]) >= 0, 'Y') as Flag

Resident Test

Order By ITEM, Deadline;

DROP Table Test;

[Anil_Babu_Samineni]

Check this

[markgraham123]

Sunny,

I tested it by adding new ID.

It is not working.

Can you please check the attachment.

[sunny_talwar · Accepted solution]

Not sure why you added ID to the sorting? This worked for me

FinalTable:

LOAD *,

  If(ITEM = Previous(ITEM), RangeSum(Peek('AccReq'), [Req.]), [Req.]) as AccReq,

  If(Avail. - If(ITEM = Previous(ITEM), RangeSum(Peek('AccReq'), [Req.]), [Req.]) >= 0, 'Y') as Flag

Resident Test

Order By ITEM, Deadline;

or may be you need this?

FinalTable:

LOAD *,

  If(ITEM = Previous(ITEM), RangeSum(Peek('AccReq'), [Req.]), [Req.]) as AccReq,

  If(Avail. - If(ITEM = Previous(ITEM), RangeSum(Peek('AccReq'), [Req.]), [Req.]) >= 0, 'Y') as Flag

Resident Test

Order By ITEM, Deadline, ID;

[sunny_talwar]

or this:

FinalTable:

LOAD *,

  If(ITEM = Previous(ITEM), RangeSum(Peek('AccReq'), [Req.]), [Req.]) as AccReq,

  If(Avail. - If(ITEM = Previous(ITEM), RangeSum(Peek('AccReq'), [Req.]), [Req.]) >= 0, 'Y') as Flag

Resident Test

Order By ITEM, ID;

[markgraham123]

Sunny,

Just FYI..

Our main point of allocation is from deadline date.

We should use Deadline too in the Order by right?

Just wondering