Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Attend QlikWorld 2020 and hear keynote speaker, Malcolm Gladwell. Register by February 29th to save $200. Learn More
dgallese
New Contributor

rangesum in load

Vorrei ottenere la colonna F

A        B             C              D      E        F

CliFat Data DaySum saldo progr progr2
55 20150102 20150102 -2 -2 208
55 20150103 20150103 70 68 210
55 20150105 20150105 100 168 140
55 20150107 20150107 30 198 40
55 20150108 20150108 10 208 10
88 20150102 20150102 100 100 80
88 20150103 20150103 -20 80 -20

Source:
LOAD * FROM Tab.qvd (qvd);
T1:
load distinct CliFat Resident Source;
join(T1)
load distinct [Data] Resident Source;

left join(T1) load * Resident Source;

drop table Source;

Result:
load
[Data]
, CliFat
, alt(Saldo,0) as DaySumS
, if(previous(CliFat)=CliFat, rangesum(Saldo, peek(Progr)),alt(Saldo,0)) as Progr
Resident T1
order by CliFat, [Data];

Tags (3)
1 Solution

Accepted Solutions
Highlighted

Re: rangesum in load

You're right Robert, I messed columns letters, then Daniela can use:

T1:

LOAD CliFat,

     Data,

     DaySum,

     Saldo

FROM

[.\data.xlsx]

(ooxml, embedded labels, table is data);

Result:

load

[Data]

, CliFat

, alt(Saldo,0) as DaySumS

, if(previous(CliFat)=CliFat, rangesum(Saldo, peek(Progr)),alt(Saldo,0)) as Progr

Resident T1

order by CliFat, [Data];

left join (Result)

load

[Data]

, CliFat

, if(previous(CliFat)=CliFat, rangesum(Saldo, peek(Progr2)),alt(Saldo,0)) as Progr2

Resident T1

order by CliFat, [Data] desc;

DROP Table T1;

View solution in original post

5 Replies
Highlighted

Re: rangesum in load

Hi Daniela, I create an Excel file with your sample data and your code for the result table works, maybe is something messed when creating the T1 table, can you upload something of your Tab.qvd to check the complete code?

I used this script:

T1:

LOAD CliFat,

     Data,

     DaySum,

     Saldo

FROM

[.\data.xlsx]

(ooxml, embedded labels, table is data);

Result:

load

[Data]

, CliFat

, alt(Saldo,0) as DaySumS

, if(previous(CliFat)=CliFat, rangesum(Saldo, peek(Progr)),alt(Saldo,0)) as Progr

Resident T1

order by CliFat, [Data];

DROP Table T1;

And Result table is loaded like this:

Highlighted

Re: rangesum in load

I assume Daniela needs progr2 (column F) which is as I thought current day-previous but some of rows are ok some not.

Daniela how did you get the 208 in first row of column F?

Highlighted

Re: rangesum in load

You're right Robert, I messed columns letters, then Daniela can use:

T1:

LOAD CliFat,

     Data,

     DaySum,

     Saldo

FROM

[.\data.xlsx]

(ooxml, embedded labels, table is data);

Result:

load

[Data]

, CliFat

, alt(Saldo,0) as DaySumS

, if(previous(CliFat)=CliFat, rangesum(Saldo, peek(Progr)),alt(Saldo,0)) as Progr

Resident T1

order by CliFat, [Data];

left join (Result)

load

[Data]

, CliFat

, if(previous(CliFat)=CliFat, rangesum(Saldo, peek(Progr2)),alt(Saldo,0)) as Progr2

Resident T1

order by CliFat, [Data] desc;

DROP Table T1;

View solution in original post

Highlighted
dgallese
New Contributor

Re: rangesum in load

CliFat Data DaySum saldo progr progr2
55 20150102 20150102 -2 -2 208 =SOMMA(D2:D6)
55 20150103 20150103 70 68 210 =SOMMA(D3:D6)
55 20150105 20150105 100 168 140 =SOMMA(D4:D6)
55 20150107 20150107 30 198 40 =SOMMA(D5:D6)
55 20150108 20150108 10 208 10 =SOMMA(D6:D6)
88 20150102 20150102 100 100 80 =SOMMA(D7:D8)
88 20150103 20150103 -20 80 -20 =SOMMA(D8:D8)

progr2 è la somma di tutte le righe che seguono fino al cambio CliFat

Highlighted
dgallese
New Contributor

Re: rangesum in load

Perfect!

thank's