topic Re: script grouping doubt in QlikView

script grouping doubt

juagarti — Fri, 04 Nov 2016 10:25:06 GMT

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!

Re: script grouping doubt

el_aprendiz111 — Fri, 04 Nov 2016 22:36:42 GMT

Hi Juan

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

Re: script grouping doubt

MarcoWedel — Sun, 06 Nov 2016 20:15:07 GMT

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

Re: script grouping doubt

juagarti — Mon, 07 Nov 2016 09:59:19 GMT

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

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, 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!!

Re: script grouping doubt

MarcoWedel — Mon, 07 Nov 2016 20:45:26 GMT

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

Re: script grouping doubt

juagarti — Tue, 15 Nov 2016 15:50:40 GMT

this run perfectly!!! Thanks very much!!

Re: script grouping doubt

MarcoWedel — Tue, 15 Nov 2016 22:53:29 GMT

you're welcome

nice that it worked for you

regards

Marco