Skip to Content
avatar image
Former Member

Aggregation functions not providing expected results in claculation view

Hi Experts,

Need your expertise to clear my doubts regarding aggregation node in calculation view .

I have a dataset which is at item level .Need to get the unique records from it by aggregating the base records. Need to get the count of some intermediate attribute and then find MAX of the count of the intermediate value. This needs to be done at 3 steps.

1. Aggregate the base set by selecting only those records which are required for next level.

2. Out the records obtained in step 1 create a calculated column to obtain to obtain an attribute and then find the number of times (count )the attribute appears in the dataset obtained in step1 .

3.Use the result set obtained in step 2 to find the record whose count (obtained in previous step)values is MAX

The issue is I am able to obtain the proper aggregation in step 1 and count in step 2 but when I perform max operation of the count value in a second calculation view , the view returns sum of count

The step by step screenshot along with the result of that node is attached.

Can you please comment if the output appearing as shown in step 3 is correct and what i am expecting (attached in the screenshot ) needs to be obtained by some other means .

The DB version is

1.00.122.07.1486663129

Studio Version: 2.3.13

Best Regards

Rohit

projection-1.jpg (190.3 kB)
projection-1-op.jpg (183.8 kB)
aggregation-1.jpg (136.2 kB)
aggregation-1.jpg (136.2 kB)
semanticscv1.jpg (122.9 kB)
op-cv1.jpg (79.6 kB)
cv-2-logic.jpg (183.7 kB)
Add comment
10|10000 characters needed characters exceeded

  • Former Member

    Do you have to retrive only 1 row that is the top value of calculated value?

  • Former Member

    Yes , I require a single row in , where the value of the count column is maximum as shown in the screenshot CV_2_expected_op.

    The input would be result as shown in screenshot op_cv1 ie

    MANDT =310

    GPART=x

    VKONT=y

    CC_COUNT=3

    Best Regards

    Rohit

  • Get RSS Feed

5 Answers

  • Best Answer
    avatar image
    Former Member
    Apr 20, 2017 at 07:27 AM

    Hi Rohit,

    1. Generate a dummy calculated column below rank block that is equal to 'A' (CHAR 1)

    2. Use rank, order by count descending, group by dummy
    3. Set threshold (fixed) to 1


    Example tutorial:

    https://www.youtube.com/watch?v=yA3qaTVSFVg

    Regards,
    Mateusz.

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Apr 19, 2017 at 12:57 PM

    Dear Rohit,

    use Rank, not aggregation in the last step.

    Regards,
    Mateusz.

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Apr 19, 2017 at 04:33 PM

    Hi Mateusz,

    And thanks a lot for the effort you have been putting :)

    I tried with a rank node in the 2nd CV . Over here in the rank node i took MANDT , GPART and VKONT as partition and order by as CC_COUNT . In this case the node is aggregating the CC_COUNT as sum even though I am not explicitly aggregating the set and final data set is appearing as pasted in screen shot.

    Pls consider 28 , same as 0000000028 .

    Best Regards

    Rohit

    Add comment
    10|10000 characters needed characters exceeded

  • Apr 19, 2017 at 05:16 PM

    Hi Rohit,

    Please check enclosed screenshot and do necessary changes in RANK node. Please select proper partition by columns. I hope, this will solve your problem.

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      Thanks Vikram for the suggestion ,

      I did the what you suggested , but still the data set is providing incorrect O/P . The iput data set by grouped or partitioned by MANDT , GPART and VKONT where it was ordered by CC_COUNT .

      Best Regards

      Rohit

  • Apr 19, 2017 at 05:31 PM

    Hi Rohit, How does the data look like when you preview it from Projection1 on TEST_2. My guess is since you are using the TEST_1 in the projection.The optimzer ignores the projection node and passes all values to the aggregation node which calculates MAX for each line and the adds them up. Again, I am guessing :) You can check it if you visualize the plan.

    After-all, you dont need two separate calc. views to accomplish this. Include one more aggregation node in your first view and calculate the MAX in the final node there.

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      Hi Benidict ,

      Thanks for spending time and putting you effort on the issue .

      Data Preview from projection 1 shows the aggregated of all the rows

      Though while checking the explain plan for the statement

      SELECT * FROM "_SYS_BIC"."ROHIT_TEST/CC_FREQUENT_PAD_V2/dp/Projection_1"
      WHERE ("GPART" = '28');

      Three rows are estimated from cv_1 to cv_2 though it should be more than that .Also all the unwanted columns ie AUGDT and FAEDN are also taking part in the the projection though they are not requested and distinct operation is done on them .

      Also attached complete explain plan below .explain-plan.png

      As suggested by you I also tried doing that in a single calculation view as well . But in this case I am unable to get the count of the record set at the intermediate level by using a aggregation node .As a last resort I found the count in using a counter in the semantics of the first cv .

      Steps followed when tring using a single calculation view as suggested :-

      1. Keeping only those columns which are required for grouping and aggregating against a calculated measure (i.e 1 ), it gives us the distinct the count of grouped record set .You can see that in the aggregation -1-op screenshot .But then the problem appears , when I try the further aggregate the above result set , group byed by MANDT,GPART,VKONT and setting count(CC_CD ) , I get the count as 1 .

      I tried another approach , by creating a calculated measure (ie 1) and summing it against

      MANDT,GPART,VKONT,CC_CD still the sum(calculated measure ) returns 1 .

      Whatever I do , the node brings all the underlying column into group by clause even if it is not used.

      Looking forward for further insights .

      Best Regards

      Rohit