cancel
Showing results for 
Search instead for 
Did you mean: 

How to count distinct records based on some condition

former_member184624
Participant
0 Kudos

Hi HANA Experts,

I want to count the distinct records based on some conditions. I tried with Counter, but it is not working as expected.

Please find the sample data.

I want to count the distinct records based on the below condition.

For counting Distinct Sales Order Number.

IF("RECORD_TYPE" ='SO' AND ("SO_NUMBER" != '') AND "FLAG" = ' ')

For counting Distinct Service Order Number

IF("RECORD_TYPE" ='SO' AND ("SR_NUMBER" != ' ') AND SO_STATUS = 'CLOSED')

Pls guide, how to achieve.

Thanks.

michael_eaton3
Active Contributor
0 Kudos

In a spreadsheet, SELECT query, calculation view? 😉

former_member184624
Participant
0 Kudos

Hi Michael,

Thank you for your message. I am unable to send Excel file through scn as it is not supported. Please let me know, how to share.

Thanks

former_member184624
Participant
0 Kudos

Hii,

Just want to know, how to apply some conditions on some attributes and want to count distinct values.

I tried with calculated attributes and included in counter but values are not matching.

Thanks

0 Kudos

the answers are misleading I think, you are trying to do it in a graphical calculation view right? I see everyone posting sql.

former_member184624
Participant
0 Kudos

Yes i am trying to implement through calculation view.

Accepted Solutions (0)

Answers (2)

Answers (2)

venkateswaran_k
Active Contributor
0 Kudos

Hi

Try using case statement

SELECT col1, col2
COUNT(CASE WHEN <your condition1> THEN 1 ELSE 0 END) AS "Heading 1"
COUNT(CASE WHEN <your condition2> THEN 1 ELSE 0 END) AS "Heading 2"
FROM <your table>
WHERE <condition>
GROUP BY col1, col2<br>

Likr in your requirement, the case statement would be

COUNT (CASE WHEN (RECORD_TYPE ='SO' AND SO_NUMBER != '' AND "FLAG" = ' ') then 1 else 0 end)

Regards,

Venkat

former_member184624
Participant
0 Kudos

Thank you venkat for your query. As you suggested, I have written the IF condition in the Calculated column and values are populating. But while keeping both calculated column in the output, the values are changing. I will the backend data.

I have created two calculated columns. 1. Counting the Sales Orders. 2. Counting the Service Orders. Below is the condition i have written for two calculated columns. (WC_SAPSN - Sales Order, WC_SRPNO - Service Order, WC_RTYFL - Record type.

SO COUNT Cal_Column (CKF53): IF("WC_RTYFL" ='SO' AND ("WC_SAPSN" != '') AND "WC_VSOFG" ='',1,0)

SR COUNT Cal_column (CKF55): IF("WC_RTYFL" ='SO' AND ("WC_SRPNO" != '') AND "WC_SOSTA" ='CLOSED',1,0)

Backend data :

HANA Sematics data:

As per the above snap, SR Count should be 1 and SO Count will be 1 (one SO having the flag X, so we should not consider.) But in HANA output, i am getting SR count is showing 2 and SO is 1. If i keep SR count alone in output, i am getting correct value ie) 1. If i include SR Count and SO count, SR count is showing wrong value ie) 2. I want to show distinct Count of SR.

Thanks.

venkateswaran_k
Active Contributor
0 Kudos

Hi

Can you pl show your sql. Did you add the Group by statement ?

venkateswaran_k
Active Contributor
0 Kudos

Hi Did you add the group by and verify it?

former_member184624
Participant
0 Kudos

Hi Venkat,

Sorry for the late reply. Please let me know, how to apply group by statement in calculation view.

Thanks.

venkateswaran_k
Active Contributor
0 Kudos

In the semantics column of your calculated column, I think there should be option for aggregation.

former_member184624
Participant
0 Kudos

Hi HANA Experts,

Can you help on this issue. Using Counter, it will count the distinct records. But i want restrict some records (In my example, i want to restrict "open" status. and want to count only closed one. Right now, COUNTER is counting for SR as 3 (1001,1002 and 1003). But expecting as 2. ie) 1001 and 1002 only. Want to apply condition. I want to do many calculation on many fields. So i dont want to apply filter in the below layer. Please suggest, how to achieve this.

How to count the distrinct records with conditions. How to achieve this in graphical view.

Thanks

ThorstenHoefer
Active Contributor
0 Kudos

In SQL, you can use:

 select count( distinct <column>)

https://help.sap.com/docs/HANA_SERVICE_CF/7c78579ce9b14a669c1f3295b0d8ca16/28c3b573c4354f75a1931202e...

Regards
Thorsten

former_member184624
Participant
0 Kudos

Thank you Thorsten.