Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Help with rangesum in load

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!

1 Solution

Accepted Solutions
Not applicable
Author

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

View solution in original post

4 Replies
Anonymous
Not applicable
Author

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
Partner - Champion
Partner - Champion

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

Not applicable
Author

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

Not applicable
Author

Thank you very much, this helped!  Richard