topic Re: rangesum in load in QlikView

rangesum in load

Anonymous — Tue, 17 Mar 2015 13:41:02 GMT

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

Re: rangesum in load

rubenmarin — Tue, 17 Mar 2015 14:21:14 GMT

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:

Re: rangesum in load

robert_mika — Tue, 17 Mar 2015 15:11:36 GMT

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?

Re: rangesum in load

rubenmarin — Tue, 17 Mar 2015 15:17:57 GMT

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;

Re: rangesum in load

Anonymous — Tue, 17 Mar 2015 15:43:06 GMT

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

Re: rangesum in load

Anonymous — Tue, 17 Mar 2015 16:12:14 GMT

Perfect!

thank's