HANA modelling aggregation issue

[former_member604551]

Hi Friends,

I am trying to achieve following requirement.

I want one extra coloum showing final status, that should be derived from the Status coloumn, for example, lets say I have three coloums that I am fetching(Complain ID, Item no, and Status) in table.

For Complaint ID 100001, there are two line items both with status as 'Accepted', so in this case I want to populate final status as 'Fully Accepted'.

In second scenario , complaint ID 100002 has just one line item , and it has status as 'Rejected'. In this case I want Final status as 'Fully Rejected'.

And third scenario, for complaint ID 100003, there is 'Rejected' and 'Accepted' status both present, so final status should be 'Partially Rejected'.

I am not sure how can we achieve this. Is it possible to do using aggregation function. If yes kindly suggest how I should do it.

Thanks a lot.

[lbreddemann]

Hi there,

nice real life use case of a complex aggregation function that includes multiple aggregation levels (in SAP BW terms similar to constant selection) and non-numeric aggregation logic.

Here's my go at it (with some other sample data):

create column table item_and_whole 
    (complaint_id integer, item_id integer, status varchar(30));

truncate table item_and_whole;    
insert into item_and_whole values (1, 1, 'accepted');
insert into item_and_whole values (1, 2, 'accepted');
insert into item_and_whole values (2, 1, 'accepted');
insert into item_and_whole values (2, 2, 'rejected');
insert into item_and_whole values (3, 1, 'rejected');
insert into item_and_whole values (3, 2, 'rejected');
insert into item_and_whole values (3, 3, 'rejected');

So far, so good - all three cases are in there.

Now, my approach looks at the number of different status values per complaint_id and also gets the "maximum" status.

This is merely to be able to distinguish between the cases where all items share the same status, but could be used otherwise as well.

In the final selection list I decode the two aggregate information via a case statement:

select iaw.complaint_id, iaw.item_id, iaw.status, st.max_stat, st.dist_stat,
       case
            when (st.dist_stat = 1)
                then
                 'All items ' || st.max_stat
            when (st.dist_stat > 1) 
                then
                 ' partially rejected'
            else
                 ' mixed status '        
       end  as complaint_status
from 
    item_and_whole iaw 
    inner join 
    (select st.complaint_id
            , max(st.status) as max_stat
            , count(distinct st.status) as dist_stat
     from item_and_whole st
     group by st.complaint_id) st
     on iaw.complaint_id = st.complaint_id
order by complaint_id, item_id;

COMPLAINT_ID	ITEM_ID	STATUS	MAX_STAT	DIST_STAT	COMPLAINT_STATUS
1	1	accepted	accepted	1	All items accepted
1	2	accepted	accepted	1	All items accepted
2	1	accepted	rejected	2	partially rejected
2	2	rejected	rejected	2	partially rejected
3	1	rejected	rejected	1	All items rejected
3	2	rejected	rejected	1	All items rejected
3	3	rejected	rejected	1	All items rejected

Depending on your use case, you might consider putting this into a function.

[former_member604551]

Hi Lars,

Thanks for the explanation, I will try it out. Also is it possible to do same by using graphical modelling? as in graphical modelling we have aggregation note? Thing is, this is just the small part of my complete scenario that I have done in graphical modelling and being new in this field I am not sure can I combine SQL and graphical mapping. Kindly suggest.

[lbreddemann]

It would be possible to build this also in the graphical modeler, but I doubt that it will be easier for you.

Look, SAP HANA data processing modelling is not equal to building a report. It's a lot more technical and it is very much required that you have a good understanding on SQL and the concepts of joins, aggregation, set operations etc.

If this is a too steep learning curve right now, you might be better off using a more reporting oriented toolset (BO, BW query designer, crystal reports...)

[former_member604551]

Yes Lars,

You are right, through Graphical Modelling it will not be easy. I got one logic that I can use with Graphical modelling where I will be using Aggregation note two time, Logic is like,

First I will aggregate the table without line item, then will take one calculate coloum with value 1, and then again aggregate, this time without the status coloum.

If the count is two , I can say that it is 'Partially Rejected' and for all those with count one, I can read the status and Like wise populate the final coloum. Issue is, Aggregation note is not working two times, I mean first time it is correctly aggregating if I remove line item, but second time when I remove status, the count filed is not added even though aggregation is done.

Not sure what I am doing wrong.

Main Table:

First Aggregation without  item no.

Second Aggregation without Status:

After second aggregation I am getting all 1 not sure what I am doing wrong.