Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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

1 Solution

Accepted Solutions
rubenmarin

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
rubenmarin

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:

robert_mika
Master III
Master III

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?

rubenmarin

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;

Anonymous
Not applicable
Author

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

Anonymous
Not applicable
Author

Perfect!

thank's