Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Highlighted
markgraham123
Valued Contributor

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

Re: Use dates in data to create filter

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

Re: Use dates in data to create filter

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

Sergey_Shuklin
Valued Contributor

Re: Use dates in data to create filter

Hello, Mark!

Like this?

dim_limits.png

markgraham123
Valued Contributor

Re: Use dates in data to create filter

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

Re: Use dates in data to create filter

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;

Re: Use dates in data to create filter

Check this

Capture.PNG

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
Valued Contributor

Re: Use dates in data to create filter

Sunny,

I tested it by adding new ID.

It is not working.

Can you please check the attachment.

Re: Use dates in data to create filter

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

Re: Use dates in data to create filter

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
Valued Contributor

Re: Use dates in data to create filter

Sunny,

Just FYI..

Our main point of allocation is from deadline date.

We should use Deadline too in the Order by right?

Just wondering