on 04-03-2012 11:29 PM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Please check my blog which might address your issue.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hello Abhijit,
I am not 100% sure but you need to go for SQL Scripting rather than graphical to achieve the same.
Regards,Neha
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
User | Count |
---|---|
88 | |
23 | |
11 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.