How to calculate sum of last two months in script

[Anonymous]

Hi,

If I have one more dimension (employee), how to calculate sum of two last month for each employee?

Tab:

LOAD * Inline [

Data , Month, Employee

2, 1, a

2, 1,b

4, 2,a

5, 2,c

1, 3,a

2, 4,b

];

Thanks,

Tu Nguyen

[antoniotiman · Accepted solution]

May be this

Tab:
LOAD * Inline [
Data , Month, Employee
2, 1, a
2, 1,b
4, 2,a
5, 2,c
1, 3,a
2, 4,b
];
Left Join (Tab)
LOAD Employee,RangeSum(FirstSortedValue(Data,-Month),FirstSortedValue(Data,-Month,2)) as SumMonth
Resident Tab
Group by Employee;

[Anonymous]

Thank you.

It work useful for that case.

I have another question.

I have sample data as below, so the question is in 3 previous days having sales or not?

Data2:

Load * Inline [

month, sales

1, 1

2, 1

4, 0

5, 0

7, 1

9, 1

10, 1];

And the expected result as image:

[antoniotiman]

Why month 8 sales 1 e months 9/10 sales 0  in Final Table ?

[Anonymous]

Sorry,  I miss data. The data load should be:

Data2:

Load * Inline [

month, sales

1, 1

2, 1

3, 0

4, 0

5, 0

6, 0

7, 1

8, 1

9, 0

10, 0];

Thank you.

[antoniotiman]

First sample data

Data2:
Load * Inline [
month, sales
1, 1
2, 1
4, 0
5, 0
7, 1
9, 1
10, 1];
Temp:
Noconcatenate
LOAD sales,month,Alt(Peek(month),month) as M1
Resident Data2
Order By month desc;
Drop Table Data2;
Table:
Concatenate
LOAD 0 as sales,month+IterNo()as month
Resident Temp
While month+IterNo() < M1;
Left Join (Temp)
LOAD month,If(RangeSum(sales,Previous(sales),Previous(Previous(sales))) > 0,1,0) as HavingSales_3_previous_month
Resident Temp
Order By month;

[antoniotiman]

2nd data sample (doesn't need to generate missing data)

Data2:
Load * Inline [
month, sales
1, 1
2, 1
3, 0
4, 0
5, 0
6, 0
7, 1
8, 1
9, 0
10, 0];
Left Join (Data2)
LOAD month,If(RangeSum(sales,Previous(sales),Previous(Previous(sales))) > 0,1,0) as HavingSales_3_previous_month
Resident Data2
Order By month;

[Anonymous]

It's great! It's helpful for me.

I have 2 question on this:

- If I apply this for a large table (about 500.000 rows), does it work well?

- If I have one more dimension ( employee - about 300.000 employees) and field [month] now is [date] ( data for 3 month, so about 90 date) => total row is 27.000.000 rows, does it work well?

Thank you so much.

[antoniotiman]

Order By in Script (Load, no Select from DB) is very slow.

[Anonymous]

So is there any another way or solution for this?