cancel
Showing results for 
Search instead for 
Did you mean: 

How to Substract Values in a single column with single reference number

0 Kudos

Hi all,

I have a issue like, I have a Material number and Number of days.

I just want to subtract Min value from Max value and want to store the resultant in another column.

MATERIALDAYS
19395100
1939550
1939567
19395800
19395760
19395450
19395240
19395345

Anyone can help me out.

Thanks,

GnR

Accepted Solutions (0)

Answers (1)

Answers (1)

lbreddemann
Active Contributor
0 Kudos

Goodness...

This is a pretty loose requirement, not to say: are you sure you know what you want to do?

And why you would want to do it?

Ok, first of all: please post the SQL code to create your example instead of the data table.

There's nothing we can really do with your data. Give us the SQL code to create the table and the data and we have something to work with.

Being a nice guy today, I did that for you this time:


create column table mat_days ( material integer, days integer);

insert into mat_days values (19395, 100);

insert into mat_days values (19395, 50);

insert into mat_days values (19395, 67);

insert into mat_days values (19395, 800);

insert into mat_days values (19395, 760);

insert into mat_days values (19395, 450);

insert into mat_days values (19395, 240);

insert into mat_days values (19395, 345);

Now to your request.
You talked about the difference of minimum and maximum days. I assume you meant "per material".

This is then really simple basic SQL 101 and would look like this:


select material, max(days) max_days, min(days) min_days, max(days) - min(days) diff_min_max

from mat_days

group by material;

MATERIALMAX_DAYSMIN_DAYSDIFF_MIN_MAX
19395  800    50      750        

Actually, this is so basic, that you should revisit your SQL course if this troubled you.

Anyhow, you also asked to store the result of the subtraction.

Why would you want to do that?

SAP HANA provides you with enough performance to calculate this on the fly. Even for lots of data.

Storing something like this is pretty much an anti-pattern for SAP HANA.

Therefore it would be really good to have a reason for this.

- Lars

0 Kudos

Dear Lars,

Thanks for the Solution.

I am specifically looking out for developing the Model graphically as some of functions has been implemented graphically.

Is there any Logic /  Function in Graphical usage.

Thanks,

GnR

lbreddemann
Active Contributor
0 Kudos

Two options:

1) you model this in a graphical calc view. There are aggregation nodes, join nodes and computed columns. Should be not too difficult.

2) you embed the SQL I gave you into a scripted calc view and use this in your graphical calc view.

- Lars

Former Member
0 Kudos

As suggested by Lars....You achieve the above requirement by using Calc view:

Agg_2

Agg_3

Join

Create an calculated column in final aggregation node

Semantics:

Output:

0 Kudos

Dear Lars,

Awesome solution from you.

Thank you so much for the help.

Thanks,

gnr

0 Kudos

Deepak,

Very Nice explanation.

it didn't strikes to me.

Thank you so much for your timely help.

Thanks,

GnR