Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi wonderful qlik experts,
what i have:
1. all the calendar dates
2. all the employee numbers which are active
2. no of leaves taken on relevant date. for example if a person has taken a leave for 5 days from 1 april 2014 to 5 april 2014, then for all these dates there will be 5 against it.
what i want to achieve:
the accumulated value for all the leaves as shown in the pictures attached.
there is one more addition to the accumulation that when the new year starts, it should start accumulation for that year
for example, if the accumulated value is 35 till 31 dec, 2014 then it should start accumulating again from 1 jan, 2015 and not add the value from the last year.
Best regards,
Try this:
TEMP:
LOAD *,
Year(TEMP_DATE) as YEAR
INLINE [
TEMP_DATE, EMP_ID, LEAVES, ACCUMULATION
1/1/2014, 1234, 0, 0
1/2/2014, 1234, 0, 0
1/3/2014, 1234, 0, 0
1/4/2014, 1234, 0, 0
1/5/2014, 1234, 0, 0
1/6/2014, 1234, 0, 0
1/7/2014, 1234, 0, 0
1/8/2014, 1234, 0, 0
1/9/2014, 1234, 0, 0
1/10/2014, 1234, 0, 0
1/11/2014, 1234, 0, 0
1/12/2014, 1234, 0, 0
1/13/2014, 1234, 0, 0
1/14/2014, 1234, 0, 0
1/15/2014, 1234, 0, 0
1/16/2014, 1234, 0, 0
1/17/2014, 1234, 0, 0
1/18/2014, 1234, 0, 0
1/19/2014, 1234, 0, 0
1/20/2014, 1234, 0, 0
1/21/2014, 1234, 0, 0
1/22/2014, 1234, 0, 0
1/23/2014, 1234, 0, 0
1/24/2014, 1234, 0, 0
1/25/2014, 1234, 0, 0
1/26/2014, 1234, 0, 0
1/27/2014, 1234, 0, 0
1/28/2014, 1234, 0, 0
1/29/2014, 1234, 0, 0
1/30/2014, 1234, 0, 0
1/31/2014, 1234, 0, 0
2/1/2014, 1234, 0, 0
2/2/2014, 1234, 0, 0
2/3/2014, 1234, 0, 0
2/4/2014, 1234, 0, 0
2/5/2014, 1234, 0, 0
2/6/2014, 1234, 0, 0
2/7/2014, 1234, 0, 0
2/8/2014, 1234, 0, 0
2/9/2014, 1234, 0, 0
2/10/2014, 1234, 0, 0
2/11/2014, 1234, 0, 0
2/12/2014, 1234, 0, 0
2/13/2014, 1234, 0, 0
2/14/2014, 1234, 0, 0
2/15/2014, 1234, 0, 0
2/16/2014, 1234, 0, 0
2/17/2014, 1234, 0, 0
2/18/2014, 1234, 0, 0
2/19/2014, 1234, 0, 0
2/20/2014, 1234, 0, 0
2/21/2014, 1234, 0, 0
2/22/2014, 1234, 0, 0
2/23/2014, 1234, 0, 0
2/24/2014, 1234, 0, 0
2/25/2014, 1234, 0, 0
2/26/2014, 1234, 0, 0
2/27/2014, 1234, 0, 0
2/28/2014, 1234, 0, 0
3/1/2014, 1234, 0, 0
3/2/2014, 1234, 0, 0
3/3/2014, 1234, 0, 0
3/4/2014, 1234, 0, 0
3/5/2014, 1234, 0, 0
3/6/2014, 1234, 0, 0
3/7/2014, 1234, 0, 0
3/8/2014, 1234, 0, 0
3/9/2014, 1234, 0, 0
3/10/2014, 1234, 0, 0
3/11/2014, 1234, 0, 0
3/12/2014, 1234, 0, 0
3/13/2014, 1234, 12, 12
3/14/2014, 1234, 12, 12
3/15/2014, 1234, 12, 12
3/16/2014, 1234, 12, 12
3/17/2014, 1234, 12, 12
3/18/2014, 1234, 12, 12
3/19/2014, 1234, 12, 12
3/20/2014, 1234, 12, 12
3/21/2014, 1234, 12, 12
3/22/2014, 1234, 12, 12
3/23/2014, 1234, 12, 12
3/24/2014, 1234, 12, 12
3/25/2014, 1234, 0, 12
3/26/2014, 1234, 0, 12
3/27/2014, 1234, 0, 12
3/28/2014, 1234, 0, 12
3/29/2014, 1234, 0, 12
3/30/2014, 1234, 0, 12
3/31/2014, 1234, 0, 12
4/1/2014, 1234, 0, 12
4/2/2014, 1234, 0, 12
4/3/2014, 1234, 0, 12
4/4/2014, 1234, 0, 12
4/5/2014, 1234, 0, 12
4/6/2014, 1234, 0, 12
4/7/2014, 1234, 0, 12
4/8/2014, 1234, 0, 12
4/9/2014, 1234, 0, 12
4/10/2014, 1234, 0, 12
4/11/2014, 1234, 0, 12
4/12/2014, 1234, 0, 12
4/13/2014, 1234, 0, 12
4/14/2014, 1234, 0, 12
4/15/2014, 1234, 0, 12
4/16/2014, 1234, 0, 12
4/17/2014, 1234, 0, 12
4/18/2014, 1234, 0, 12
4/19/2014, 1234, 0, 12
4/20/2014, 1234, 0, 12
4/21/2014, 1234, 0, 12
4/22/2014, 1234, 0, 12
4/23/2014, 1234, 0, 12
4/24/2014, 1234, 0, 12
4/25/2014, 1234, 0, 12
4/26/2014, 1234, 0, 12
4/27/2014, 1234, 0, 12
4/28/2014, 1234, 0, 12
4/29/2014, 1234, 0, 12
4/30/2014, 1234, 0, 12
5/1/2014, 1234, 0, 12
5/2/2014, 1234, 0, 12
5/3/2014, 1234, 0, 12
5/4/2014, 1234, 0, 12
5/5/2014, 1234, 0, 12
5/6/2014, 1234, 0, 12
5/7/2014, 1234, 0, 12
5/8/2014, 1234, 0, 12
5/9/2014, 1234, 0, 12
5/10/2014, 1234, 0, 12
5/11/2014, 1234, 0, 12
5/12/2014, 1234, 0, 12
5/13/2014, 1234, 0, 12
5/14/2014, 1234, 0, 12
5/15/2014, 1234, 0, 12
5/16/2014, 1234, 0, 12
5/17/2014, 1234, 0, 12
5/18/2014, 1234, 0, 12
5/19/2014, 1234, 0, 12
5/20/2014, 1234, 0, 12
5/21/2014, 1234, 0, 12
5/22/2014, 1234, 0, 12
5/23/2014, 1234, 0, 12
5/24/2014, 1234, 0, 12
5/25/2014, 1234, 0, 12
5/26/2014, 1234, 0, 12
5/27/2014, 1234, 0, 12
5/28/2014, 1234, 0, 12
5/29/2014, 1234, 0, 12
5/30/2014, 1234, 0, 12
5/31/2014, 1234, 0, 12
6/1/2014, 1234, 0, 12
6/2/2014, 1234, 0, 12
6/3/2014, 1234, 0, 12
6/4/2014, 1234, 0, 12
6/5/2014, 1234, 0, 12
6/6/2014, 1234, 0, 12
6/7/2014, 1234, 0, 12
6/8/2014, 1234, 0, 12
6/9/2014, 1234, 0, 12
6/10/2014, 1234, 0, 12
6/11/2014, 1234, 0, 12
6/12/2014, 1234, 0, 12
6/13/2014, 1234, 0, 12
6/14/2014, 1234, 1, 13
6/15/2014, 1234, 15, 28
6/16/2014, 1234, 15, 28
6/17/2014, 1234, 15, 28
6/18/2014, 1234, 15, 28
6/19/2014, 1234, 15, 28
6/20/2014, 1234, 15, 28
6/21/2014, 1234, 15, 28
6/22/2014, 1234, 15, 28
6/23/2014, 1234, 15, 28
6/24/2014, 1234, 15, 28
6/25/2014, 1234, 15, 28
6/26/2014, 1234, 15, 28
6/27/2014, 1234, 15, 28
6/28/2014, 1234, 15, 28
6/29/2014, 1234, 15, 28
6/30/2014, 1234, 0, 28
7/1/2014, 1234, 0, 28
7/2/2014, 1234, 0, 28
7/3/2014, 1234, 0, 28
7/4/2014, 1234, 0, 28
7/5/2014, 1234, 0, 28
7/6/2014, 1234, 0, 28
7/7/2014, 1234, 0, 28
7/8/2014, 1234, 0, 28
7/9/2014, 1234, 0, 28
7/10/2014, 1234, 0, 28
7/11/2014, 1234, 0, 28
7/12/2014, 1234, 0, 28
7/13/2014, 1234, 0, 28
7/14/2014, 1234, 0, 28
7/15/2014, 1234, 0, 28
7/16/2014, 1234, 0, 28
7/17/2014, 1234, 0, 28
7/18/2014, 1234, 0, 28
7/19/2014, 1234, 0, 28
7/20/2014, 1234, 0, 28
7/21/2014, 1234, 0, 28
7/22/2014, 1234, 0, 28
7/23/2014, 1234, 0, 28
7/24/2014, 1234, 0, 28
7/25/2014, 1234, 0, 28
7/26/2014, 1234, 0, 28
7/27/2014, 1234, 0, 28
7/28/2014, 1234, 0, 28
7/29/2014, 1234, 0, 28
7/30/2014, 1234, 0, 28
7/31/2014, 1234, 0, 28
8/1/2014, 1234, 0, 28
8/2/2014, 1234, 0, 28
8/3/2014, 1234, 0, 28
8/4/2014, 1234, 0, 28
8/5/2014, 1234, 0, 28
8/6/2014, 1234, 0, 28
8/7/2014, 1234, 0, 28
8/8/2014, 1234, 0, 28
8/9/2014, 1234, 0, 28
8/10/2014, 1234, 0, 28
8/11/2014, 1234, 0, 28
8/12/2014, 1234, 0, 28
8/13/2014, 1234, 0, 28
8/14/2014, 1234, 0, 28
8/15/2014, 1234, 0, 28
8/16/2014, 1234, 0, 28
8/17/2014, 1234, 0, 28
8/18/2014, 1234, 0, 28
8/19/2014, 1234, 0, 28
8/20/2014, 1234, 0, 28
8/21/2014, 1234, 0, 28
8/22/2014, 1234, 0, 28
8/23/2014, 1234, 0, 28
8/24/2014, 1234, 0, 28
8/25/2014, 1234, 0, 28
8/26/2014, 1234, 0, 28
8/27/2014, 1234, 0, 28
8/28/2014, 1234, 0, 28
8/29/2014, 1234, 0, 28
8/30/2014, 1234, 0, 28
8/31/2014, 1234, 0, 28
9/1/2014, 1234, 0, 28
9/2/2014, 1234, 0, 28
9/3/2014, 1234, 0, 28
9/4/2014, 1234, 0, 28
9/5/2014, 1234, 0, 28
9/6/2014, 1234, 0, 28
9/7/2014, 1234, 0, 28
9/8/2014, 1234, 0, 28
9/9/2014, 1234, 0, 28
9/10/2014, 1234, 0, 28
9/11/2014, 1234, 0, 28
9/12/2014, 1234, 0, 28
9/13/2014, 1234, 0, 28
9/14/2014, 1234, 0, 28
9/15/2014, 1234, 0, 28
9/16/2014, 1234, 0, 28
9/17/2014, 1234, 0, 28
9/18/2014, 1234, 0, 28
9/19/2014, 1234, 0, 28
9/20/2014, 1234, 0, 28
9/21/2014, 1234, 0, 28
9/22/2014, 1234, 0, 28
9/23/2014, 1234, 0, 28
9/24/2014, 1234, 0, 28
9/25/2014, 1234, 0, 28
9/26/2014, 1234, 0, 28
9/27/2014, 1234, 0, 28
9/28/2014, 1234, 0, 28
9/29/2014, 1234, 0, 28
9/30/2014, 1234, 0, 28
10/1/2014, 1234, 0, 28
10/2/2014, 1234, 0, 28
10/3/2014, 1234, 0, 28
10/4/2014, 1234, 0, 28
10/5/2014, 1234, 0, 28
10/6/2014, 1234, 0, 28
10/7/2014, 1234, 0, 28
10/8/2014, 1234, 0, 28
10/9/2014, 1234, 0, 28
10/10/2014, 1234, 0, 28
10/11/2014, 1234, 0, 28
10/12/2014, 1234, 0, 28
10/13/2014, 1234, 0, 28
10/14/2014, 1234, 0, 28
10/15/2014, 1234, 0, 28
10/16/2014, 1234, 0, 28
10/17/2014, 1234, 0, 28
10/18/2014, 1234, 0, 28
10/19/2014, 1234, 0, 28
10/20/2014, 1234, 0, 28
10/21/2014, 1234, 0, 28
10/22/2014, 1234, 0, 28
10/23/2014, 1234, 0, 28
10/24/2014, 1234, 0, 28
10/25/2014, 1234, 0, 28
10/26/2014, 1234, 0, 28
10/27/2014, 1234, 0, 28
10/28/2014, 1234, 0, 28
10/29/2014, 1234, 0, 28
10/30/2014, 1234, 0, 28
10/31/2014, 1234, 0, 28
11/1/2014, 1234, 0, 28
11/2/2014, 1234, 0, 28
11/3/2014, 1234, 0, 28
11/4/2014, 1234, 0, 28
11/5/2014, 1234, 0, 28
11/6/2014, 1234, 0, 28
11/7/2014, 1234, 0, 28
11/8/2014, 1234, 0, 28
11/9/2014, 1234, 0, 28
11/10/2014, 1234, 0, 28
11/11/2014, 1234, 0, 28
11/12/2014, 1234, 0, 28
11/13/2014, 1234, 0, 28
11/14/2014, 1234, 0, 28
11/15/2014, 1234, 0, 28
11/16/2014, 1234, 0, 28
11/17/2014, 1234, 0, 28
11/18/2014, 1234, 0, 28
11/19/2014, 1234, 0, 28
11/20/2014, 1234, 0, 28
11/21/2014, 1234, 0, 28
11/22/2014, 1234, 0, 28
11/23/2014, 1234, 0, 28
11/24/2014, 1234, 0, 28
11/25/2014, 1234, 0, 28
11/26/2014, 1234, 0, 28
11/27/2014, 1234, 0, 28
11/28/2014, 1234, 0, 28
11/29/2014, 1234, 0, 28
11/30/2014, 1234, 0, 28
12/1/2014, 1234, 0, 28
12/2/2014, 1234, 0, 28
12/3/2014, 1234, 0, 28
12/4/2014, 1234, 0, 28
12/5/2014, 1234, 0, 28
12/6/2014, 1234, 0, 28
12/7/2014, 1234, 0, 28
12/8/2014, 1234, 0, 28
12/9/2014, 1234, 0, 28
12/10/2014, 1234, 0, 28
12/11/2014, 1234, 0, 28
12/12/2014, 1234, 0, 28
12/13/2014, 1234, 0, 28
12/14/2014, 1234, 0, 28
12/15/2014, 1234, 0, 28
12/16/2014, 1234, 0, 28
12/17/2014, 1234, 0, 28
12/18/2014, 1234, 0, 28
12/19/2014, 1234, 0, 28
12/20/2014, 1234, 0, 28
12/21/2014, 1234, 0, 28
12/22/2014, 1234, 0, 28
12/23/2014, 1234, 0, 28
12/24/2014, 1234, 0, 28
12/25/2014, 1234, 0, 28
12/26/2014, 1234, 0, 28
12/27/2014, 1234, 0, 28
12/28/2014, 1234, 0, 28
12/29/2014, 1234, 0, 28
12/30/2014, 1234, 0, 28
12/31/2014, 1234, 0, 28
1/1/2015, 1234, 0, 0
1/2/2015, 1234, 0, 0
1/3/2015, 1234, 0, 0
1/4/2015, 1234, 0, 0
1/5/2015, 1234, 0, 0
1/6/2015, 1234, 0, 0
1/7/2015, 1234, 0, 0
1/8/2015, 1234, 0, 0
1/9/2015, 1234, 0, 0
1/10/2015, 1234, 0, 0
1/11/2015, 1234, 0, 0
1/12/2015, 1234, 0, 0
1/13/2015, 1234, 0, 0
1/14/2015, 1234, 0, 0
1/15/2015, 1234, 0, 0
1/16/2015, 1234, 0, 0
1/17/2015, 1234, 0, 0
1/18/2015, 1234, 0, 0
1/19/2015, 1234, 0, 0
1/20/2015, 1234, 30, 30
1/21/2015, 1234, 30, 30
1/22/2015, 1234, 30, 30
1/23/2015, 1234, 30, 30
1/24/2015, 1234, 30, 30
1/25/2015, 1234, 30, 30
1/26/2015, 1234, 30, 30
1/27/2015, 1234, 30, 30
1/28/2015, 1234, 30, 30
1/29/2015, 1234, 30, 30
1/30/2015, 1234, 30, 30
1/31/2015, 1234, 30, 30
2/1/2015, 1234, 30, 30
2/2/2015, 1234, 30, 30
2/3/2015, 1234, 30, 30
2/4/2015, 1234, 30, 30
2/5/2015, 1234, 30, 30
2/6/2015, 1234, 30, 30
2/7/2015, 1234, 30, 30
2/8/2015, 1234, 30, 30
2/9/2015, 1234, 30, 30
2/10/2015, 1234, 30, 30
2/11/2015, 1234, 30, 30
2/12/2015, 1234, 30, 30
2/13/2015, 1234, 30, 30
2/14/2015, 1234, 30, 30
2/15/2015, 1234, 30, 30
2/16/2015, 1234, 30, 30
2/17/2015, 1234, 30, 30
2/18/2015, 1234, 30, 30
2/19/2015, 1234, 0, 30
2/20/2015, 1234, 0, 30
2/21/2015, 1234, 0, 30
2/22/2015, 1234, 0, 30
2/23/2015, 1234, 0, 30
2/24/2015, 1234, 0, 30
2/25/2015, 1234, 0, 30
2/26/2015, 1234, 0, 30
2/27/2015, 1234, 0, 30
2/28/2015, 1234, 0, 30
3/1/2015, 1234, 0, 30
3/2/2015, 1234, 0, 30
3/3/2015, 1234, 0, 30
3/4/2015, 1234, 0, 30
3/5/2015, 1234, 0, 30
3/6/2015, 1234, 0, 30
3/7/2015, 1234, 0, 30
3/8/2015, 1234, 0, 30
3/9/2015, 1234, 0, 30
3/10/2015, 1234, 0, 30
3/11/2015, 1234, 0, 30
3/12/2015, 1234, 0, 30
3/13/2015, 1234, 0, 30
3/14/2015, 1234, 0, 30
3/15/2015, 1234, 0, 30
3/16/2015, 1234, 0, 30
3/17/2015, 1234, 0, 30
3/18/2015, 1234, 0, 30
3/19/2015, 1234, 0, 30
3/20/2015, 1234, 0, 30
3/21/2015, 1234, 0, 30
3/22/2015, 1234, 0, 30
3/23/2015, 1234, 0, 30
3/24/2015, 1234, 0, 30
3/25/2015, 1234, 0, 30
3/26/2015, 1234, 0, 30
3/27/2015, 1234, 0, 30
3/28/2015, 1234, 0, 30
3/29/2015, 1234, 0, 30
3/30/2015, 1234, 0, 30
3/31/2015, 1234, 0, 30
4/1/2015, 1234, 0, 30
4/2/2015, 1234, 0, 30
4/3/2015, 1234, 0, 30
4/4/2015, 1234, 0, 30
4/5/2015, 1234, 0, 30
4/6/2015, 1234, 0, 30
4/7/2015, 1234, 0, 30
4/8/2015, 1234, 0, 30
4/9/2015, 1234, 0, 30
4/10/2015, 1234, 0, 30
4/11/2015, 1234, 0, 30
4/12/2015, 1234, 0, 30
4/13/2015, 1234, 0, 30
4/14/2015, 1234, 0, 30
4/15/2015, 1234, 0, 30
4/16/2015, 1234, 0, 30
4/17/2015, 1234, 0, 30
4/18/2015, 1234, 0, 30
4/19/2015, 1234, 0, 30
4/20/2015, 1234, 0, 30
4/21/2015, 1234, 0, 30
4/22/2015, 1234, 0, 30
4/23/2015, 1234, 0, 30
4/24/2015, 1234, 0, 30
4/25/2015, 1234, 0, 30
4/26/2015, 1234, 0, 30
4/27/2015, 1234, 0, 30
4/28/2015, 1234, 0, 30
4/29/2015, 1234, 0, 30
4/30/2015, 1234, 0, 30
5/1/2015, 1234, 0, 30
5/2/2015, 1234, 0, 30
5/3/2015, 1234, 0, 30
5/4/2015, 1234, 0, 30
5/5/2015, 1234, 0, 30
5/6/2015, 1234, 0, 30
5/7/2015, 1234, 0, 30
5/8/2015, 1234, 0, 30
5/9/2015, 1234, 0, 30
5/10/2015, 1234, 0, 30
5/11/2015, 1234, 0, 30
5/12/2015, 1234, 0, 30
5/13/2015, 1234, 0, 30
5/14/2015, 1234, 0, 30
5/15/2015, 1234, 0, 30
5/16/2015, 1234, 0, 30
5/17/2015, 1234, 0, 30
5/18/2015, 1234, 0, 30
5/19/2015, 1234, 0, 30
5/20/2015, 1234, 0, 30
5/21/2015, 1234, 0, 30
5/22/2015, 1234, 0, 30
5/23/2015, 1234, 0, 30
5/24/2015, 1234, 0, 30
5/25/2015, 1234, 0, 30
5/26/2015, 1234, 0, 30
5/27/2015, 1234, 0, 30
5/28/2015, 1234, 0, 30
5/29/2015, 1234, 0, 30
5/30/2015, 1234, 0, 30
5/31/2015, 1234, 0, 30
];
Final:
LOAD TEMP_DATE,
EMP_ID,
LEAVES,
YEAR,
If(EMP_ID = Peek('EMP_ID') and YEAR = Peek('YEAR'),
If(LEAVES = 0,
RangeSum(Peek('SCRIPT_ACCU'), LEAVES),
If(LEAVES = PEEK('LEAVES'),
RangeSum(Peek('SCRIPT_ACCU'), 0),
RangeSum(Peek('SCRIPT_ACCU'), LEAVES))), LEAVES) AS SCRIPT_ACCU,
ACCUMULATION
Resident TEMP
Order By EMP_ID, TEMP_DATE;
DROP Table TEMP;
Do you have a sample data you can share? Would be easier to help you with some sample data.
Hi ,
Try like this,
Expression:
RangeSum(Above(Sum(Leaves), 0, (RowNo(TOTAL) - (Year - Min(TOTAL Year))* 12)))
Keep no accumulation for expression.
else,
Post sample app.
HTH,
Hirish
sample data attached in the inline format
Try this:
TEMP:
LOAD *,
Year(TEMP_DATE) as YEAR
INLINE [
TEMP_DATE, EMP_ID, LEAVES, ACCUMULATION
1/1/2014, 1234, 0, 0
1/2/2014, 1234, 0, 0
1/3/2014, 1234, 0, 0
1/4/2014, 1234, 0, 0
1/5/2014, 1234, 0, 0
1/6/2014, 1234, 0, 0
1/7/2014, 1234, 0, 0
1/8/2014, 1234, 0, 0
1/9/2014, 1234, 0, 0
1/10/2014, 1234, 0, 0
1/11/2014, 1234, 0, 0
1/12/2014, 1234, 0, 0
1/13/2014, 1234, 0, 0
1/14/2014, 1234, 0, 0
1/15/2014, 1234, 0, 0
1/16/2014, 1234, 0, 0
1/17/2014, 1234, 0, 0
1/18/2014, 1234, 0, 0
1/19/2014, 1234, 0, 0
1/20/2014, 1234, 0, 0
1/21/2014, 1234, 0, 0
1/22/2014, 1234, 0, 0
1/23/2014, 1234, 0, 0
1/24/2014, 1234, 0, 0
1/25/2014, 1234, 0, 0
1/26/2014, 1234, 0, 0
1/27/2014, 1234, 0, 0
1/28/2014, 1234, 0, 0
1/29/2014, 1234, 0, 0
1/30/2014, 1234, 0, 0
1/31/2014, 1234, 0, 0
2/1/2014, 1234, 0, 0
2/2/2014, 1234, 0, 0
2/3/2014, 1234, 0, 0
2/4/2014, 1234, 0, 0
2/5/2014, 1234, 0, 0
2/6/2014, 1234, 0, 0
2/7/2014, 1234, 0, 0
2/8/2014, 1234, 0, 0
2/9/2014, 1234, 0, 0
2/10/2014, 1234, 0, 0
2/11/2014, 1234, 0, 0
2/12/2014, 1234, 0, 0
2/13/2014, 1234, 0, 0
2/14/2014, 1234, 0, 0
2/15/2014, 1234, 0, 0
2/16/2014, 1234, 0, 0
2/17/2014, 1234, 0, 0
2/18/2014, 1234, 0, 0
2/19/2014, 1234, 0, 0
2/20/2014, 1234, 0, 0
2/21/2014, 1234, 0, 0
2/22/2014, 1234, 0, 0
2/23/2014, 1234, 0, 0
2/24/2014, 1234, 0, 0
2/25/2014, 1234, 0, 0
2/26/2014, 1234, 0, 0
2/27/2014, 1234, 0, 0
2/28/2014, 1234, 0, 0
3/1/2014, 1234, 0, 0
3/2/2014, 1234, 0, 0
3/3/2014, 1234, 0, 0
3/4/2014, 1234, 0, 0
3/5/2014, 1234, 0, 0
3/6/2014, 1234, 0, 0
3/7/2014, 1234, 0, 0
3/8/2014, 1234, 0, 0
3/9/2014, 1234, 0, 0
3/10/2014, 1234, 0, 0
3/11/2014, 1234, 0, 0
3/12/2014, 1234, 0, 0
3/13/2014, 1234, 12, 12
3/14/2014, 1234, 12, 12
3/15/2014, 1234, 12, 12
3/16/2014, 1234, 12, 12
3/17/2014, 1234, 12, 12
3/18/2014, 1234, 12, 12
3/19/2014, 1234, 12, 12
3/20/2014, 1234, 12, 12
3/21/2014, 1234, 12, 12
3/22/2014, 1234, 12, 12
3/23/2014, 1234, 12, 12
3/24/2014, 1234, 12, 12
3/25/2014, 1234, 0, 12
3/26/2014, 1234, 0, 12
3/27/2014, 1234, 0, 12
3/28/2014, 1234, 0, 12
3/29/2014, 1234, 0, 12
3/30/2014, 1234, 0, 12
3/31/2014, 1234, 0, 12
4/1/2014, 1234, 0, 12
4/2/2014, 1234, 0, 12
4/3/2014, 1234, 0, 12
4/4/2014, 1234, 0, 12
4/5/2014, 1234, 0, 12
4/6/2014, 1234, 0, 12
4/7/2014, 1234, 0, 12
4/8/2014, 1234, 0, 12
4/9/2014, 1234, 0, 12
4/10/2014, 1234, 0, 12
4/11/2014, 1234, 0, 12
4/12/2014, 1234, 0, 12
4/13/2014, 1234, 0, 12
4/14/2014, 1234, 0, 12
4/15/2014, 1234, 0, 12
4/16/2014, 1234, 0, 12
4/17/2014, 1234, 0, 12
4/18/2014, 1234, 0, 12
4/19/2014, 1234, 0, 12
4/20/2014, 1234, 0, 12
4/21/2014, 1234, 0, 12
4/22/2014, 1234, 0, 12
4/23/2014, 1234, 0, 12
4/24/2014, 1234, 0, 12
4/25/2014, 1234, 0, 12
4/26/2014, 1234, 0, 12
4/27/2014, 1234, 0, 12
4/28/2014, 1234, 0, 12
4/29/2014, 1234, 0, 12
4/30/2014, 1234, 0, 12
5/1/2014, 1234, 0, 12
5/2/2014, 1234, 0, 12
5/3/2014, 1234, 0, 12
5/4/2014, 1234, 0, 12
5/5/2014, 1234, 0, 12
5/6/2014, 1234, 0, 12
5/7/2014, 1234, 0, 12
5/8/2014, 1234, 0, 12
5/9/2014, 1234, 0, 12
5/10/2014, 1234, 0, 12
5/11/2014, 1234, 0, 12
5/12/2014, 1234, 0, 12
5/13/2014, 1234, 0, 12
5/14/2014, 1234, 0, 12
5/15/2014, 1234, 0, 12
5/16/2014, 1234, 0, 12
5/17/2014, 1234, 0, 12
5/18/2014, 1234, 0, 12
5/19/2014, 1234, 0, 12
5/20/2014, 1234, 0, 12
5/21/2014, 1234, 0, 12
5/22/2014, 1234, 0, 12
5/23/2014, 1234, 0, 12
5/24/2014, 1234, 0, 12
5/25/2014, 1234, 0, 12
5/26/2014, 1234, 0, 12
5/27/2014, 1234, 0, 12
5/28/2014, 1234, 0, 12
5/29/2014, 1234, 0, 12
5/30/2014, 1234, 0, 12
5/31/2014, 1234, 0, 12
6/1/2014, 1234, 0, 12
6/2/2014, 1234, 0, 12
6/3/2014, 1234, 0, 12
6/4/2014, 1234, 0, 12
6/5/2014, 1234, 0, 12
6/6/2014, 1234, 0, 12
6/7/2014, 1234, 0, 12
6/8/2014, 1234, 0, 12
6/9/2014, 1234, 0, 12
6/10/2014, 1234, 0, 12
6/11/2014, 1234, 0, 12
6/12/2014, 1234, 0, 12
6/13/2014, 1234, 0, 12
6/14/2014, 1234, 1, 13
6/15/2014, 1234, 15, 28
6/16/2014, 1234, 15, 28
6/17/2014, 1234, 15, 28
6/18/2014, 1234, 15, 28
6/19/2014, 1234, 15, 28
6/20/2014, 1234, 15, 28
6/21/2014, 1234, 15, 28
6/22/2014, 1234, 15, 28
6/23/2014, 1234, 15, 28
6/24/2014, 1234, 15, 28
6/25/2014, 1234, 15, 28
6/26/2014, 1234, 15, 28
6/27/2014, 1234, 15, 28
6/28/2014, 1234, 15, 28
6/29/2014, 1234, 15, 28
6/30/2014, 1234, 0, 28
7/1/2014, 1234, 0, 28
7/2/2014, 1234, 0, 28
7/3/2014, 1234, 0, 28
7/4/2014, 1234, 0, 28
7/5/2014, 1234, 0, 28
7/6/2014, 1234, 0, 28
7/7/2014, 1234, 0, 28
7/8/2014, 1234, 0, 28
7/9/2014, 1234, 0, 28
7/10/2014, 1234, 0, 28
7/11/2014, 1234, 0, 28
7/12/2014, 1234, 0, 28
7/13/2014, 1234, 0, 28
7/14/2014, 1234, 0, 28
7/15/2014, 1234, 0, 28
7/16/2014, 1234, 0, 28
7/17/2014, 1234, 0, 28
7/18/2014, 1234, 0, 28
7/19/2014, 1234, 0, 28
7/20/2014, 1234, 0, 28
7/21/2014, 1234, 0, 28
7/22/2014, 1234, 0, 28
7/23/2014, 1234, 0, 28
7/24/2014, 1234, 0, 28
7/25/2014, 1234, 0, 28
7/26/2014, 1234, 0, 28
7/27/2014, 1234, 0, 28
7/28/2014, 1234, 0, 28
7/29/2014, 1234, 0, 28
7/30/2014, 1234, 0, 28
7/31/2014, 1234, 0, 28
8/1/2014, 1234, 0, 28
8/2/2014, 1234, 0, 28
8/3/2014, 1234, 0, 28
8/4/2014, 1234, 0, 28
8/5/2014, 1234, 0, 28
8/6/2014, 1234, 0, 28
8/7/2014, 1234, 0, 28
8/8/2014, 1234, 0, 28
8/9/2014, 1234, 0, 28
8/10/2014, 1234, 0, 28
8/11/2014, 1234, 0, 28
8/12/2014, 1234, 0, 28
8/13/2014, 1234, 0, 28
8/14/2014, 1234, 0, 28
8/15/2014, 1234, 0, 28
8/16/2014, 1234, 0, 28
8/17/2014, 1234, 0, 28
8/18/2014, 1234, 0, 28
8/19/2014, 1234, 0, 28
8/20/2014, 1234, 0, 28
8/21/2014, 1234, 0, 28
8/22/2014, 1234, 0, 28
8/23/2014, 1234, 0, 28
8/24/2014, 1234, 0, 28
8/25/2014, 1234, 0, 28
8/26/2014, 1234, 0, 28
8/27/2014, 1234, 0, 28
8/28/2014, 1234, 0, 28
8/29/2014, 1234, 0, 28
8/30/2014, 1234, 0, 28
8/31/2014, 1234, 0, 28
9/1/2014, 1234, 0, 28
9/2/2014, 1234, 0, 28
9/3/2014, 1234, 0, 28
9/4/2014, 1234, 0, 28
9/5/2014, 1234, 0, 28
9/6/2014, 1234, 0, 28
9/7/2014, 1234, 0, 28
9/8/2014, 1234, 0, 28
9/9/2014, 1234, 0, 28
9/10/2014, 1234, 0, 28
9/11/2014, 1234, 0, 28
9/12/2014, 1234, 0, 28
9/13/2014, 1234, 0, 28
9/14/2014, 1234, 0, 28
9/15/2014, 1234, 0, 28
9/16/2014, 1234, 0, 28
9/17/2014, 1234, 0, 28
9/18/2014, 1234, 0, 28
9/19/2014, 1234, 0, 28
9/20/2014, 1234, 0, 28
9/21/2014, 1234, 0, 28
9/22/2014, 1234, 0, 28
9/23/2014, 1234, 0, 28
9/24/2014, 1234, 0, 28
9/25/2014, 1234, 0, 28
9/26/2014, 1234, 0, 28
9/27/2014, 1234, 0, 28
9/28/2014, 1234, 0, 28
9/29/2014, 1234, 0, 28
9/30/2014, 1234, 0, 28
10/1/2014, 1234, 0, 28
10/2/2014, 1234, 0, 28
10/3/2014, 1234, 0, 28
10/4/2014, 1234, 0, 28
10/5/2014, 1234, 0, 28
10/6/2014, 1234, 0, 28
10/7/2014, 1234, 0, 28
10/8/2014, 1234, 0, 28
10/9/2014, 1234, 0, 28
10/10/2014, 1234, 0, 28
10/11/2014, 1234, 0, 28
10/12/2014, 1234, 0, 28
10/13/2014, 1234, 0, 28
10/14/2014, 1234, 0, 28
10/15/2014, 1234, 0, 28
10/16/2014, 1234, 0, 28
10/17/2014, 1234, 0, 28
10/18/2014, 1234, 0, 28
10/19/2014, 1234, 0, 28
10/20/2014, 1234, 0, 28
10/21/2014, 1234, 0, 28
10/22/2014, 1234, 0, 28
10/23/2014, 1234, 0, 28
10/24/2014, 1234, 0, 28
10/25/2014, 1234, 0, 28
10/26/2014, 1234, 0, 28
10/27/2014, 1234, 0, 28
10/28/2014, 1234, 0, 28
10/29/2014, 1234, 0, 28
10/30/2014, 1234, 0, 28
10/31/2014, 1234, 0, 28
11/1/2014, 1234, 0, 28
11/2/2014, 1234, 0, 28
11/3/2014, 1234, 0, 28
11/4/2014, 1234, 0, 28
11/5/2014, 1234, 0, 28
11/6/2014, 1234, 0, 28
11/7/2014, 1234, 0, 28
11/8/2014, 1234, 0, 28
11/9/2014, 1234, 0, 28
11/10/2014, 1234, 0, 28
11/11/2014, 1234, 0, 28
11/12/2014, 1234, 0, 28
11/13/2014, 1234, 0, 28
11/14/2014, 1234, 0, 28
11/15/2014, 1234, 0, 28
11/16/2014, 1234, 0, 28
11/17/2014, 1234, 0, 28
11/18/2014, 1234, 0, 28
11/19/2014, 1234, 0, 28
11/20/2014, 1234, 0, 28
11/21/2014, 1234, 0, 28
11/22/2014, 1234, 0, 28
11/23/2014, 1234, 0, 28
11/24/2014, 1234, 0, 28
11/25/2014, 1234, 0, 28
11/26/2014, 1234, 0, 28
11/27/2014, 1234, 0, 28
11/28/2014, 1234, 0, 28
11/29/2014, 1234, 0, 28
11/30/2014, 1234, 0, 28
12/1/2014, 1234, 0, 28
12/2/2014, 1234, 0, 28
12/3/2014, 1234, 0, 28
12/4/2014, 1234, 0, 28
12/5/2014, 1234, 0, 28
12/6/2014, 1234, 0, 28
12/7/2014, 1234, 0, 28
12/8/2014, 1234, 0, 28
12/9/2014, 1234, 0, 28
12/10/2014, 1234, 0, 28
12/11/2014, 1234, 0, 28
12/12/2014, 1234, 0, 28
12/13/2014, 1234, 0, 28
12/14/2014, 1234, 0, 28
12/15/2014, 1234, 0, 28
12/16/2014, 1234, 0, 28
12/17/2014, 1234, 0, 28
12/18/2014, 1234, 0, 28
12/19/2014, 1234, 0, 28
12/20/2014, 1234, 0, 28
12/21/2014, 1234, 0, 28
12/22/2014, 1234, 0, 28
12/23/2014, 1234, 0, 28
12/24/2014, 1234, 0, 28
12/25/2014, 1234, 0, 28
12/26/2014, 1234, 0, 28
12/27/2014, 1234, 0, 28
12/28/2014, 1234, 0, 28
12/29/2014, 1234, 0, 28
12/30/2014, 1234, 0, 28
12/31/2014, 1234, 0, 28
1/1/2015, 1234, 0, 0
1/2/2015, 1234, 0, 0
1/3/2015, 1234, 0, 0
1/4/2015, 1234, 0, 0
1/5/2015, 1234, 0, 0
1/6/2015, 1234, 0, 0
1/7/2015, 1234, 0, 0
1/8/2015, 1234, 0, 0
1/9/2015, 1234, 0, 0
1/10/2015, 1234, 0, 0
1/11/2015, 1234, 0, 0
1/12/2015, 1234, 0, 0
1/13/2015, 1234, 0, 0
1/14/2015, 1234, 0, 0
1/15/2015, 1234, 0, 0
1/16/2015, 1234, 0, 0
1/17/2015, 1234, 0, 0
1/18/2015, 1234, 0, 0
1/19/2015, 1234, 0, 0
1/20/2015, 1234, 30, 30
1/21/2015, 1234, 30, 30
1/22/2015, 1234, 30, 30
1/23/2015, 1234, 30, 30
1/24/2015, 1234, 30, 30
1/25/2015, 1234, 30, 30
1/26/2015, 1234, 30, 30
1/27/2015, 1234, 30, 30
1/28/2015, 1234, 30, 30
1/29/2015, 1234, 30, 30
1/30/2015, 1234, 30, 30
1/31/2015, 1234, 30, 30
2/1/2015, 1234, 30, 30
2/2/2015, 1234, 30, 30
2/3/2015, 1234, 30, 30
2/4/2015, 1234, 30, 30
2/5/2015, 1234, 30, 30
2/6/2015, 1234, 30, 30
2/7/2015, 1234, 30, 30
2/8/2015, 1234, 30, 30
2/9/2015, 1234, 30, 30
2/10/2015, 1234, 30, 30
2/11/2015, 1234, 30, 30
2/12/2015, 1234, 30, 30
2/13/2015, 1234, 30, 30
2/14/2015, 1234, 30, 30
2/15/2015, 1234, 30, 30
2/16/2015, 1234, 30, 30
2/17/2015, 1234, 30, 30
2/18/2015, 1234, 30, 30
2/19/2015, 1234, 0, 30
2/20/2015, 1234, 0, 30
2/21/2015, 1234, 0, 30
2/22/2015, 1234, 0, 30
2/23/2015, 1234, 0, 30
2/24/2015, 1234, 0, 30
2/25/2015, 1234, 0, 30
2/26/2015, 1234, 0, 30
2/27/2015, 1234, 0, 30
2/28/2015, 1234, 0, 30
3/1/2015, 1234, 0, 30
3/2/2015, 1234, 0, 30
3/3/2015, 1234, 0, 30
3/4/2015, 1234, 0, 30
3/5/2015, 1234, 0, 30
3/6/2015, 1234, 0, 30
3/7/2015, 1234, 0, 30
3/8/2015, 1234, 0, 30
3/9/2015, 1234, 0, 30
3/10/2015, 1234, 0, 30
3/11/2015, 1234, 0, 30
3/12/2015, 1234, 0, 30
3/13/2015, 1234, 0, 30
3/14/2015, 1234, 0, 30
3/15/2015, 1234, 0, 30
3/16/2015, 1234, 0, 30
3/17/2015, 1234, 0, 30
3/18/2015, 1234, 0, 30
3/19/2015, 1234, 0, 30
3/20/2015, 1234, 0, 30
3/21/2015, 1234, 0, 30
3/22/2015, 1234, 0, 30
3/23/2015, 1234, 0, 30
3/24/2015, 1234, 0, 30
3/25/2015, 1234, 0, 30
3/26/2015, 1234, 0, 30
3/27/2015, 1234, 0, 30
3/28/2015, 1234, 0, 30
3/29/2015, 1234, 0, 30
3/30/2015, 1234, 0, 30
3/31/2015, 1234, 0, 30
4/1/2015, 1234, 0, 30
4/2/2015, 1234, 0, 30
4/3/2015, 1234, 0, 30
4/4/2015, 1234, 0, 30
4/5/2015, 1234, 0, 30
4/6/2015, 1234, 0, 30
4/7/2015, 1234, 0, 30
4/8/2015, 1234, 0, 30
4/9/2015, 1234, 0, 30
4/10/2015, 1234, 0, 30
4/11/2015, 1234, 0, 30
4/12/2015, 1234, 0, 30
4/13/2015, 1234, 0, 30
4/14/2015, 1234, 0, 30
4/15/2015, 1234, 0, 30
4/16/2015, 1234, 0, 30
4/17/2015, 1234, 0, 30
4/18/2015, 1234, 0, 30
4/19/2015, 1234, 0, 30
4/20/2015, 1234, 0, 30
4/21/2015, 1234, 0, 30
4/22/2015, 1234, 0, 30
4/23/2015, 1234, 0, 30
4/24/2015, 1234, 0, 30
4/25/2015, 1234, 0, 30
4/26/2015, 1234, 0, 30
4/27/2015, 1234, 0, 30
4/28/2015, 1234, 0, 30
4/29/2015, 1234, 0, 30
4/30/2015, 1234, 0, 30
5/1/2015, 1234, 0, 30
5/2/2015, 1234, 0, 30
5/3/2015, 1234, 0, 30
5/4/2015, 1234, 0, 30
5/5/2015, 1234, 0, 30
5/6/2015, 1234, 0, 30
5/7/2015, 1234, 0, 30
5/8/2015, 1234, 0, 30
5/9/2015, 1234, 0, 30
5/10/2015, 1234, 0, 30
5/11/2015, 1234, 0, 30
5/12/2015, 1234, 0, 30
5/13/2015, 1234, 0, 30
5/14/2015, 1234, 0, 30
5/15/2015, 1234, 0, 30
5/16/2015, 1234, 0, 30
5/17/2015, 1234, 0, 30
5/18/2015, 1234, 0, 30
5/19/2015, 1234, 0, 30
5/20/2015, 1234, 0, 30
5/21/2015, 1234, 0, 30
5/22/2015, 1234, 0, 30
5/23/2015, 1234, 0, 30
5/24/2015, 1234, 0, 30
5/25/2015, 1234, 0, 30
5/26/2015, 1234, 0, 30
5/27/2015, 1234, 0, 30
5/28/2015, 1234, 0, 30
5/29/2015, 1234, 0, 30
5/30/2015, 1234, 0, 30
5/31/2015, 1234, 0, 30
];
Final:
LOAD TEMP_DATE,
EMP_ID,
LEAVES,
YEAR,
If(EMP_ID = Peek('EMP_ID') and YEAR = Peek('YEAR'),
If(LEAVES = 0,
RangeSum(Peek('SCRIPT_ACCU'), LEAVES),
If(LEAVES = PEEK('LEAVES'),
RangeSum(Peek('SCRIPT_ACCU'), 0),
RangeSum(Peek('SCRIPT_ACCU'), LEAVES))), LEAVES) AS SCRIPT_ACCU,
ACCUMULATION
Resident TEMP
Order By EMP_ID, TEMP_DATE;
DROP Table TEMP;