rangesum in load

[Anonymous]

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

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

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 · Accepted solution]

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]

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]

Perfect!

thank's