Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
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

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
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