cancel
Showing results for 
Search instead for 
Did you mean: 

HANA Getting a Count of Unique values from a Column in graphical Calc View

Former Member
0 Kudos

Hello All,

I have following situation  with a HANA Calculation View output

COLUMN1   COLUMN2   COLUMN3     
TECH111      A11111      MATERAL1 
TECH111      A11112      MATERAL2
TECH111      A11112      MATERAL3
TECH111      A11113      MATERAL4
TECH112      A11113      MATERAL5
TECH112      A11114      MATERAL6
TECH112      A11115      MATERAL7
TECH112      A11116      MATERAL8

I want to get the count for each unique value papering in Column 2

i.e.A11111 appears 1 time, A11112 and A11113 appears 2 time and so on..

And In column 5 I would like to get a Total for All the instances (Sum of Column 4)

So the final output i need is shown below..

COLUMN1    COLUMN2  COLUMN3   ........COLUMN4    ........COLUMN5
TECH111      A11111      MATERAL1        1                    12
TECH111      A11112      MATERAL2        2                    12
TECH111      A11112      MATERAL3        2                    12  
TECH111      A11113      MATERAL4        2                    12
TECH112      A11113      MATERAL5        2                    12
TECH112      A11114      MATERAL6        1                    12
TECH112      A11115      MATERAL7        1                    12
TECH112      A11116      MATERAL8        1                    12

I want to achieve this inside a graphical Calculation view. I tried to create a Calculated Column for the counter but it did not work. There are very limited functions available to use inside a Calculated Column. Do I have to get this done in SQL Script Calculation view? My preference is getting it done in Graphical Calculation view.

Under Distinct values tab of data preview I can see the exact output I want to see but is there any way to have that as a result of my Calc View?

There is something called as counters in the output of a calc view. It appears to be built for this purpose but it always gives a value of 1.

I would appreciate any inputs. Ideas. Thanks.

Regards

Abhijit

Accepted Solutions (1)

Accepted Solutions (1)

hai_murali_here
Advisor
Advisor
0 Kudos

Hi Abhijit,

This can be partially achieved through Counter in Calc Views as shown below.

Lets say that you have the following table,

Create a Calc View on top of this as follows

And select only COL2 and COL3 for Output as Attribute and hide COL3 through Properties

Create Counter as shown below

Activate the Calc View and do data preview.The output will show the distinct values of Column2

Hope this helps you.

Rgds,Murali

Former Member
0 Kudos

Murali,

thanks for your answer. This was helpful.  Selecting only COL2 and COL3 works however we want all of them to be visible in the output.

We were able to achieve this using SQL. thanks again or your help.

~

Abhijit

Former Member
0 Kudos

Hi Abhijit

 

I tried to achieve this using SQL queries and the below query gave me a partial result which is shown by murali in the above post.r

select column2, count (column2) as count from counters

group by column2

if I include other columns as well then it needs to be group by them , then it is yielding to wrong result.

can you share a clue on how did you achieve it.

Thanks

Santosh

Former Member
0 Kudos

Hi

Finally I could do achieve the above using the below code snippet.But I feel this logic is complex

is there any simple way we can achieve it ,by using procedures can we achieve this simply,please share your thoughts.

drop table subcount;

drop table tempcount;

create column table subcount As (select column2,count(column2) as column4

from counters

group by column2);

create column table tempcount AS (select column1,counters.column2 ,column3,subcount.column4

from counters,subcount

where counters.column2 = subcount.column2);

select tempcount.column1,tempcount.column2,tempcount.column3,tempcount.column4,

(select  sum(tempcount.column4)

from tempcount)as column5

from tempcount

Awaiting your feedback.

Thanks

Santosh

Former Member
0 Kudos

Hi Abhijit N ,

Could you please explain me how did you achieve the above requirement with SQL??

Best Regards,

Krishna.

Former Member
0 Kudos

Hi Muralikrishnan,

          Can this done in analytic view!!

My requirement is I have 3 columns

Stylecode     vendor         turn_around

1111              AAA                5

1111              AAA                4

2222              BBB                6

2222              BBB                3

2222              BBB                4

Here,

I need the calculated measure for average turn_around for a particular vendor. That is When Vendor AAA selected the avg of turn_around(5+4 = 9/2= 4.5) as a measure and count of stylecode as another measure. that is style_count = 2 (count of stylecode of vendor AAA)

this can be done in analytic view? or any other way. Could you please help me in this.

Thank you

Mathivanan

Answers (5)

Answers (5)

Former Member
0 Kudos

Hi Abhijit,

We can do this in graphical calculation view by using aggregation.I just tried like this.In JOIN no need to join anything.If it is wrong let me know.

get unique material count in AGGREGATION_1 and total material count in AGGREGATION_2.

Regards,

Mallika

former_member226419
Contributor
0 Kudos

Hi,

I have solved the same.

Select A."COLUMN1",A."COLUMN2",A."COLUMN3",B."COUNT",sum("COUNT") over() as "Sumtotal"  from

(Select "COLUMN2", count("COLUMN2") as "COUNT" from "HANA"."group_by_1"

Group by "COLUMN2") as B

Inner join

"HANA"."group_by_1" as A

On A."COLUMN2" = B."COLUMN2" ;

BR

Sumeet

0 Kudos
Former Member
0 Kudos

Hi Abhijit,

Not sure whether this workaround will help you..

a) I am able to calculate the column4.

b) However, not able to calculate the column5 . Still not able to find the significance of the column5 as there are 8 rows and the value you want as 12 ..

a) I created an Analytical view on the table.

b) Then created a CV and two instances were added of the above AN view.

c) two projections added as shown in figure. and one aggregation.

d) join the projection and aggregation as shown.

e) renamed the row_count column to COUNT_COL2.

former_member182277
Contributor
0 Kudos

Hello Abhijit,

I am not 100% sure but you need to go for SQL Scripting rather than graphical to achieve the same.

Regards,Neha

Former Member
0 Kudos

Hi Neha,

Thank you for your response. We had to go the SQL route to achieve this. I believe Graphical Calc view has very limited functionality. Even if we were able to achieve the current requirement it gives me a feeling that if a  requirement change comes through graphical may not be able to accomodate that.

Thanks. again.

~

Abhijit