Help with rangesum in load

[#1]

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!

[Anonymous]

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

;

[CELAMBARASAN]

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);

[#4 · Accepted solution]

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

(ooxml, embedded labels);

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);

[#5]

Thank you very much, this helped!  Richard