topic Re: Help with rangesum in load in QlikView

Help with rangesum in load

Thu, 10 Oct 2013 21:49:39 GMT

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!

Re: Help with rangesum in load

Anonymous — Thu, 10 Oct 2013 22:07:06 GMT

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

;

Re: Help with rangesum in load

CELAMBARASAN — Thu, 10 Oct 2013 23:01:47 GMT

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

Re: Help with rangesum in load

Mon, 14 Oct 2013 18:55:48 GMT

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

Re: Help with rangesum in load

Mon, 14 Oct 2013 18:56:28 GMT

Thank you very much, this helped! Richard