topic Re: Use dates in data to create filter in QlikView

Use dates in data to create filter

markgraham123 — Wed, 07 Jun 2017 16:52:26 GMT

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

Re: Use dates in data to create filter

sunny_talwar — Wed, 07 Jun 2017 17:08:37 GMT

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

Re: Use dates in data to create filter

Sergey_Shuklin — Wed, 07 Jun 2017 17:19:13 GMT

Hello, Mark!

Like this?

Re: Use dates in data to create filter

markgraham123 — Wed, 07 Jun 2017 17:44:29 GMT

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

Re: Use dates in data to create filter

sunny_talwar — Wed, 07 Jun 2017 17:49:45 GMT

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

Anil_Babu_Samineni — Wed, 07 Jun 2017 17:54:38 GMT

Check this

Re: Use dates in data to create filter

markgraham123 — Wed, 07 Jun 2017 19:15:37 GMT

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

sunny_talwar — Wed, 07 Jun 2017 19:18:24 GMT

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

sunny_talwar — Wed, 07 Jun 2017 19:19:55 GMT

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;

Re: Use dates in data to create filter

markgraham123 — Wed, 07 Jun 2017 19:29:00 GMT

Sunny,

Just FYI..

Our main point of allocation is from deadline date.

We should use Deadline too in the Order by right?

Just wondering

Re: Use dates in data to create filter

markgraham123 — Wed, 07 Jun 2017 19:33:38 GMT

Trying the second condition brother!

Let me try.

Re: Use dates in data to create filter

sunny_talwar — Wed, 07 Jun 2017 19:33:40 GMT

I think so... and that is why I started with ITEM and then Deadline. Not sure if you need to sort by ID when Deadline is the same?

Re: Use dates in data to create filter

markgraham123 — Wed, 07 Jun 2017 20:02:25 GMT

Thanks a lot brother!