Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

markgraham123
Contributor II

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;

12 Replies

Re: Use dates in data to create filter

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

serj_shu
Valued Contributor

Re: Use dates in data to create filter

Hello, Mark!

Like this?

dim_limits.png

markgraham123
Contributor II

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

markgraham123
Contributor II

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;

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

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

Community Browser