script grouping doubt

[juagarti]

Hello,

I've a problem in the qlikview script. I'm extracting data from an oracle DB and I need to get some data, in the image below I describe the process

I need to sum the column NUM in groups of 4 consecutive hours. ¿How can I do that with the qlikview script?

Thanks!

[el_aprendiz111]

Hi Juan

Rangesum(Below(total sum(NUM),0,4))

[MarcoWedel]

Hi,

one script solution could be:

table1:
LOAD RecNo() as ID,
    NUM,
    HOUR,
    DAY,
    ZONE,
    DATE
FROM [https://community.qlik.com/servlet/JiveServlet/download/1154368-252382/data.xlsx] (ooxml, embedded labels, table is Hoja1);

Join (table1)
LOAD ID,
    RangeSum(NUM,Previous(NUM),Previous(Previous(NUM)),Previous(Previous(Previous(NUM)))) as TOTAL
Resident table1
Order By ID desc;

hope this helps

regards

Marco

[juagarti]

Hello,

Thanks for your answer, both run correctly and I can obtain the result. Now I have another problem. There aren´t only one zone and one day the are many days and many zone and I have to group the result with there. I have to use aggr function, for example

Rangesum(Below(total sum(NUM),0,4))  ->  Rangesum(Below(total sum( aggr(only(NUM), ZONE,DAY)),0,4))

OR

1. table1: 
2. LOAD RecNo() as ID, 
3.     NUM, 
4.     HOUR, 
5.     DAY, 
6.     ZONE, 
7.     DATE 
8. FROM [https://community.qlik.com/servlet/JiveServlet/download/1154368-252382/data.xlsx] (ooxml, embedded labels, table is Hoja1); 
9.  
10. Join (table1) 
11. LOAD ID, 
12.     RangeSum(NUM,Previous(NUM),Previous(Previous(NUM)),Previous(Previous(Previous(NUM)))) as TOTAL 
13. Resident table1 
14. Order By ID desc, Group by DAY,ZONE;

but is doesn´t work.

I need that when the day is 21,22,23 only sum the data in the same ZONE and in the same DAY. I need that the operation are group by day and zone.

I attach a new data example.

Thanks!!

[MarcoWedel · Accepted solution]

Hi,

maybe like this?

LOAD RecNo() as ID,
    NUM,
    HOUR,
    DAY,
    ZONE,
    DATE
FROM [https://community.qlik.com/servlet/JiveServlet/download/1155084-252596/data.xlsx] (ooxml, embedded labels, table is Hoja1);

Join (table1)
LOAD ID,
    RangeSum(NUM,
              If(DAY&'|'&ZONE=Previous(DAY&'|'&ZONE),Previous(NUM)),
              If(DAY&'|'&ZONE=Previous(Previous(DAY&'|'&ZONE)),Previous(Previous(NUM))),
              If(DAY&'|'&ZONE=Previous(Previous(Previous(DAY&'|'&ZONE))),Previous(Previous(Previous(NUM))))) as TOTAL
Resident table1
Order By ZONE,DAY,HOUR desc;

hope this helps

regards

Marco

[juagarti]

this run perfectly!!! Thanks very much!!

[MarcoWedel]

you're welcome

nice that it worked for you

regards

Marco