Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
markgraham123
Specialist
Specialist

Use dates in data to create filter

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.

  

IDDeadlineITEMReq.Avail.
17/10/2017A1.0016.00
27/12/2017A1.0016.00
37/15/2017A1.0016.00
47/15/2017A1.0016.00
57/15/2017A1.0016.00
67/18/2017A1.0016.00
77/18/2017A1.0016.00
87/18/2017A1.0016.00
97/19/2017A1.0016.00
107/19/2017A1.0016.00
117/20/2017A1.0016.00
127/21/2017A1.0016.00
137/21/2017A1.0016.00
147/21/2017A1.0016.00
157/22/2017A1.0016.00
167/24/2017A2.0016.00
177/24/2017A2.0016.00
187/24/2017A1.0016.00
197/24/2017A1.0016.00
207/25/2017A2.0016.00
217/25/2017A1.0016.00
227/25/2017A1.0016.00
237/26/2017A2.0016.00
247/28/2017A1.0016.00
257/29/2017A1.0016.00
268/2/2017A1.00

16.00

Any help is highly appreciated!!

Please see the attachment

1 Solution

Accepted Solutions
sunny_talwar

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;

View solution in original post

12 Replies
sunny_talwar

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

Sergey_Shuklin
Specialist
Specialist

Hello, Mark!

Like this?

dim_limits.png

markgraham123
Specialist
Specialist
Author

Here it is:

      

17/10/2017A16.001.00Y
27/12/2017A16.001.00Y
37/15/2017A16.001.00Y
47/15/2017A16.001.00Y
57/15/2017A16.001.00Y
67/18/2017A16.001.00Y
77/18/2017A16.001.00Y
87/18/2017A16.001.00Y
97/19/2017A16.001.00Y
107/19/2017A16.001.00Y
117/20/2017A16.001.00Y
127/21/2017A16.001.00Y
137/21/2017A16.001.00Y
147/21/2017A16.001.00Y
157/22/2017A16.001.00Y
167/24/2017A16.002.00
177/24/2017A16.002.00
187/24/2017A16.001.00
197/24/2017A16.001.00
207/25/2017A16.002.00
217/25/2017A16.001.00
227/25/2017A16.001.00
237/26/2017A16.002.00
247/28/2017A16.001.00
257/29/2017A16.001.00
268/2/2017A16.001.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

Capture.PNG

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
markgraham123
Specialist
Specialist
Author

Sunny,

I tested it by adding new ID.

It is not working.

Can you please check the attachment.

sunny_talwar

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
Specialist
Specialist
Author

Sunny,

Just FYI..

Our main point of allocation is from deadline date.

We should use Deadline too in the Order by right?

Just wondering