Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I need to create a new table from data similar to:
TABLE1:
Date Value Name
1/3/12 2 Bob
2/6/12 4 Sue
1/5/12 2 Sue
1/7/12 8 Bob
The new data would look like:
TABLE2:
Name Date Period NewValue
Bob 1/3/12 201201 1
Sue 1/5/12 201201 1
Sue 2/6/12 201202 1
Essentially, whenever the sum of Value in TABLE1 for any month is >0, then I need 1 (and only 1) row to be generated in TABLE2 with the NewValue of 1. I think rangesum would work but not entirely sure of the syntax.
Any help is greatly appreciated!
Thanks Celambarasan, This was the most useful in solving the problem. In the group by I needed to add a Year(Date) as well since in the actual data there are many years, small tweak! Here is the actual load in case someone is interested in the future. This works perfectly. (I used a small test excel file for the original data included in Table1 in the original question).
LOAD Date,
Value,
Name
FROM
(
TABLE2:
LOAD Name,
If(Sum(Value)>0, 1) AS NewValue,
Date(MonthStart(Min(Date)), 'YYYYMM') AS Period,
Min(Date) As NewDate
Resident TABLE1
Group by Name, Year(Date),Month(Date);
Hi Richard,
I don't know if rangesum is the right way. Try this, hope it helps you
Load TABLE1:
load
<your fields>,
year(date) & '_' & month(date) as period
from -....
TABLE2:
load *,
IF (tot_value > 0, 1, 0) as NEWVALUE
;
load
period,
sum(value) as tot_value,
first(name) as First_Name,
first(date) as First_Date
group by period;
FINALTABLE:
load *,
'1' as dummy,
resident TABLE2 where NEWVALUE = 1
;
Try something like this
LOAD
Name,
If(Sum(Value)>0, 1) AS NewValue,
Date(MonthStart(Min(Date)), 'YYYYMM') AS Period,
Min(Date) As NewDate
Resident Table1
Group by Name, Month(Date);
Thanks Celambarasan, This was the most useful in solving the problem. In the group by I needed to add a Year(Date) as well since in the actual data there are many years, small tweak! Here is the actual load in case someone is interested in the future. This works perfectly. (I used a small test excel file for the original data included in Table1 in the original question).
LOAD Date,
Value,
Name
FROM
(
TABLE2:
LOAD Name,
If(Sum(Value)>0, 1) AS NewValue,
Date(MonthStart(Min(Date)), 'YYYYMM') AS Period,
Min(Date) As NewDate
Resident TABLE1
Group by Name, Year(Date),Month(Date);
Thank you very much, this helped! Richard