Question Re: running total in Technology Q&A

running total

millicentdark — Mon, 29 Feb 2016 14:18:31 GMT

Hi Experts,

Has anyone done a running total function in SAP HANA graphical calculation view?

below is my SQL running total but i want to do same in SAP HANA ;

any idea or steps to achieve this?

Regards

Justice

Re: running total

Former Member — Mon, 29 Feb 2016 17:13:28 GMT

Just remove the ROWS UNBOUNDED PRECEEDING from your example, then it should work for SAP HANA.

Re: running total

millicentdark — Mon, 29 Feb 2016 17:17:49 GMT

hi Martin,

i am using a graphical calculation view. How do i add that in SAP HANA. what particular function should i use?

Re: running total

millicentdark — Tue, 01 Mar 2016 10:15:19 GMT

Hi Experts,

Any help on my request?

I will be glad if someone can assist me. attached is my graphical calculation views and i will need help for my expected result.

SAP HANA GRAPHICAL CALCULATION VIEW

RAW DATA PREVIEW

EXPECTED RESULT

Running total column is my expected result for my SAP HANA calculated column name.

Re: running total

Former Member — Tue, 01 Mar 2016 11:37:11 GMT

Yes, you can achieve it graphically (but performance wise not the best approach).

Projection: Add the base table (Say A)

Fields: ID, Value

Projection1: Add the base table (Say B)

Fields: ID_1, Value_1

Join: Projection <Inner join> Projection1 ( Dont join any fields, with would produce N*N cardinality)

Projection: Put filter, ID<=ID_1

This produce data like

ID	VALUE	ID_1	VALUE_1
1	10	1	10
2	20	1	10
2	20	2	20
3	30	1	10
3	30	2	20
3	30	3	30

Aggregation: insert VALUE_1 as aggregated column (deselect ID_1)

ID	VALUE	VALUE_1
1	10	10
2	20	30
3	20	60

Regards,

Deepak

Re: running total

millicentdark — Tue, 01 Mar 2016 12:07:17 GMT

Hi deepak,

i can not add filer in a projection.

Regards

Justice

Re: running total

Former Member — Tue, 01 Mar 2016 13:05:00 GMT

Add a projection node, between the final level Semantic and Join. In this Node put the filter condition.

You need to add the TransID in both of underlying projections(where the base tables exist).

At the join node, pls dont join any fields( in the snapshot i observe that the debit and credit fields are mapped)

This is what i meant

Re: running total

millicentdark — Tue, 01 Mar 2016 16:09:50 GMT

hi deepak,

below are exactly what i did; tell me if i missed something;

the column_2_1 did not perform the running total as expected.