Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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;
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:
Why month 8 sales 1 e months 9/10 sales 0 in Final Table ?
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.
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;
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;
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.
Order By in Script (Load, no Select from DB) is very slow.
So is there any another way or solution for this?