How to count distinct records based on some condition

[former_member184624]

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]

In a spreadsheet, SELECT query, calculation view? 😉

[former_member184624]

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]

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

[former_member759577]

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]

Yes i am trying to implement through calculation view.

[venkateswaran_k]

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]

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]

Hi

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

[venkateswaran_k]

Hi Did you add the group by and verify it?

[former_member184624]

Hi Venkat,

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

Thanks.

[venkateswaran_k]

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

[former_member184624]

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]

In SQL, you can use:

 select count( distinct <column>)

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

Regards
Thorsten

[former_member184624]

Thank you Thorsten.