cancel
Showing results for 
Search instead for 
Did you mean: 

Difficult to decide on which way is better

former_member224444
Participant
0 Kudos

Hi,

We are developing a report to see a car part number selling status for eg: Fast Moving, Medium Moving or Slow Moving based on business defined logic. The logic is as below.

1) Count how many sale order lines the parts are

2) Find the percentage of sale order line of each part against total number of sale order lines

3) Sort the result descending based on percentage, and mark first 80 percentage parts as Fast moving, next 15 % as medium and next 5% as Slow moving.

I can bring below result using analytical view based on sale order line item table.

But i need "Speed" Column to be calculated based on the above logic.

I am thinking of using Cursor in Scripted Calculation view to loop through the each and every record in the table and sum the percentage in each and every row  in a sum variable and mark as "Fast" till it reaches 80 if it reaches 80 then clear the sum variable, then again mark as Medium till it reaches 15 then clear the variable, then mark rest as "Slow".

Please let me know if this is right and best approach. As per my knowledge it is not possible to achieve this in Graphical view, please correct me if i am wrong. Is there any other way i can use in scripted calc view to achieve this.

Thanks

Rithika

Accepted Solutions (1)

Accepted Solutions (1)

muthuram_shanmugavel2
Contributor
0 Kudos

Hi Rithika,

This can be calculated using scripted Calc View / Stored Procedure (Can be called from Scripted CV)

without writing cursor.

As It it not involve any looping, It will be good in terms of performance.

Analytic view Result:

Logic to calculated Speed Type:

Create Procedure Proc_SPEEDTYPE_CALC (OUT OP_TT_FINAL_RESULT "INF713775"."JUL7_TT_1")

LANGUAGE SQLSCRIPT

SQL SECURITY INVOKER

As

BEGIN

Declare OP Integer;

Declare OP_FAST Integer;

Declare OP_MEDIUM Integer;

Declare OP_SLOW Integer;

Declare OP_TT_FAST "INF713775"."JUL7_TT_1";

Declare OP_TT_MEDIUM "INF713775"."JUL7_TT_1";

Declare OP_TT_SLOW "INF713775"."JUL7_TT_1";

Select count(*) into OP from Jul7_Speed;

OP_FAST = Round (0.8 * OP);

OP_MEDIUM = Round (0.15 * OP);

OP_SLOW = Round (0.05 * OP);

OP_TT_FAST = Select PartNumber, Sale_Order_Line_Count, Percentage, 'FAST' As Speed from Jul7_Speed Order By Percentage desc Limit :OP_FAST;

OP_TT_MEDIUM = Select PartNumber, Sale_Order_Line_Count, Percentage, 'MEDIUM' As Speed from Jul7_Speed Order By Percentage desc Limit :OP_MEDIUM OFFSET :OP_FAST;

OP_TT_SLOW = Select PartNumber, Sale_Order_Line_Count, Percentage, 'SLOW' As Speed from Jul7_Speed Order By Percentage Limit :OP_SLOW;

OP_TT_FINAL_RESULT = Select * from :OP_TT_FAST UNION Select * from :OP_TT_MEDIUM UNION Select * from :OP_TT_SLOW;

END



The Final Output:


former_member224444
Participant
0 Kudos

Hi Muthuram,

Thank you for the analysis. But this one will not give expected result. Below part in your code

Select count(*) into OP from Jul7_Speed;

OP_FAST = Round (0.8 * OP);

OP_MEDIUM = Round (0.15 * OP);

OP_SLOW = Round (0.05 * OP)

It will give percentage of line count in  the total number of lines for eg if total number of lines is 13 then 80% of 13 lines is 10 rows, 15 percentage is 2 rows, 5% is remaining rows.

But what i am looking for is i want to find out first set of rows which has total sum of 80 in percentage col  (0 - 80 %) , this set will be marked as FAST and next set rows contributes to sum of 15 in percentage col(81% to 95%) , this set will be marked as Medium and (96% to 100%) which is 5% marked as SLOW.

Thanks

Rithika

muthuram_shanmugavel2
Contributor
0 Kudos

Hi Rithika,

I hope you expecting like this.

Create Procedure Proc_SPEEDTYPE_CALC_2 (OUT OP_TT_FINAL_RESULT "INF713775"."JUL7_TT_2")

LANGUAGE SQLSCRIPT

SQL SECURITY INVOKER

As


BEGIN

OP_TT_FINAL_RESULT = Select PartNumber, Sale_Order_Line_Count, Percentage,

Sum(Percentage) OVER (order by PERCENTAGE desc, PartNumber) As CUMULATIVE_COUNT,

Case

When Sum(Percentage) OVER (order by PERCENTAGE desc, PartNumber) <= 80 Then

'FAST'

When Sum(Percentage) OVER (order by PERCENTAGE desc, PartNumber) > 80 AND Sum(Percentage) OVER (order by PERCENTAGE desc, PartNumber) <= 95 Then

'MEDIUM'

Else

'SLOW'

End

As Speed 

from Jul7_Speed;

END


-----------------------------------------------------------------------------


Table Type:


Create Type "INF713775"."JUL7_TT_2" As Table

(PARTNUMBER Varchar(10),

SALE_ORDER_LINE_COUNT Integer,

PERCENTAGE Integer,

CUMULATIVE_COUNT Integer,

SPEED Varchar(10))


-------------------------------------------------------------------------



Call Proc_SPEEDTYPE_CALC_2 (?)



Hope, It would meet your requirement


Regards,

Muthuram

lbreddemann
Active Contributor
0 Kudos

Maybe I'm not quite getting what you want to do, but to me this reads like you want to look into PERCENTILE functions:

with base as (select part_no, cnt,

                    (select sum(cnt) from part_cnts) tot_cnt,

                     round(( cnt/((select sum(cnt) from part_cnts)/100)),2) perc_of_tot_cnt,

                     sum(cnt) over (order by cnt desc) cumm_sum

              from part_cnts

              order by cnt desc)

select part_no, cnt, tot_cnt, perc_of_tot_cnt, cumm_sum,

      round(percentile_cont (0.8) within group (order by cumm_sum asc) over() ,2 )thres_08,

      round(percentile_cont (0.95) within group (order by cumm_sum asc) over(),2) thres_095,

    case

    when percentile_cont (0.8) within group (order by cumm_sum asc) over() >= cumm_sum then

        'FAST'

    when percentile_cont (0.95) within group (order by cumm_sum asc) over() >= cumm_sum  then

        'MEDIUM'

    else 'SLOW'

    end  grp

from base

order by cnt desc;

See infos here SAP HANA Academy - SQL Functions: PERCENTILE_CONT and PERCENTILE_DISC - YouTube and here http://help.sap.com/saphelp_hanaplatform/helpdata/en/20/a353327519101495dfd0a87060a0d3/content.htm.

This approach doesn't use the percentage calculation for the classification (but I left it in for you to compare). The idea here is to first do a cummulative sum on the cnts and to calculate the threshold values for your desired percentages (aka percentiles).

Now, all you've got to do is to check whether the thresholds are met and assign the class.

former_member224444
Participant
0 Kudos

Hi Muthuram,

You are a star . Your solution solved my issue. But i am interested to know how this window function OVER() works, how it adds the previous record with current record and is this part number in the following statement is partition by (order by PERCENTAGE desc, PartNumber).


Thanks a lot.


Regards,

Rithika

former_member224444
Participant
0 Kudos

Thank you Lars.

muthuram_shanmugavel2
Contributor
0 Kudos

Hi Rithika,

Window Function --> Sum(Percentage) OVER (Order by PERCENTAGE)

will calculate cumulative sum by column Percentage.

As per your requirement, you are calculating First 80% which part number have High Percentages.

So I used the Function.

Sum(Percentage) OVER (Order by PERCENTAGE desc)

If you note down the results,

cumulative count is calculated first record itself as 42 (summing up three 14)

So I included the Key Partnumber as well in the function

Sum(Percentage) OVER (Order by PERCENTAGE desc, PartNumber)

and hence It is giving correct results.

To know more about window Function,

Please refer the link.

Window Functions - SAP HANA SQL and System Views Reference - SAP Library

Regards,

Muthuram

former_member224444
Participant
0 Kudos

Thanks a lot Muthuram

Answers (1)

Answers (1)

lucas_oliveira
Advisor
Advisor
0 Kudos

Hello Rithika,

I'm guessing you don't actually need cursor for what you're trying to achieve as you can try using window functions to help you with that. I believe using WITH clause might be a way out as well. Hopefully I understood your scenario the right way.

I don't have your data but using the classic abap sflight data I could come up with something that you can translate to your reality and evaluate if it makes sense or not. If that's the best approach I can't confirm. I'd avoid cursors though.

Anyhow, the idea was to use table SBOOK as input and play around with the FLDATE column (which you can use it as your part number column) and I counted the number of bookids for each one of the calendar month (essentially yyyymm from FLDATE).

After calculating the individual counts (on variable v1), the total counts (on variable v2) and the percentage (on varibale v3) you can use a percent_rank window function over the percentage calculated before in order to evaluate where does the value fit (the first 80% of results, the next 15% and the remaining 5%):


DO BEGIN

  v1 = select left(replace(FLDATE,'-',''),6) as calmonth, count(BOOKID) as individual_cnt

from SAPABAP1.SBOOK group by left(replace(FLDATE,'-',''),6);

  v2 = select sum(individual_cnt) as total from :v1;

  v3 = select a.CALMONTH, a.INDIVIDUAL_CNT, b.total, round(to_decimal(a.INDIVIDUAL_CNT/b.total)*100,4) as perc

from :v1 a cross join :v2 b;

  select CALMONTH, INDIVIDUAL_CNT, PERC, RANK,

(CASE WHEN RANK<=0.8 THEN 'Fast'

WHEN RANK > 0.8 and RANK <= 0.95 THEN 'Medium'

ELSE 'Slow' END) as RANK_STR

from (select CALMONTH, INDIVIDUAL_CNT, PERC, percent_rank() over (order by perc desc) as rank

from :v3 order by perc desc);

END;

This will result in something like:


CALMONTH;INDIVIDUAL_CNT;PERC  ;RANK                 ;RANK_STR

201307  ;261595        ;0.6262;0.0                  ;Fast 

201308  ;261494        ;0.626 ;0.0055248618784530384;Fast 

200608  ;261482        ;0.626 ;0.0055248618784530384;Fast 

200307  ;261419        ;0.6258;0.016574585635359115 ;Fast 

[...]

200909  ;229991        ;0.5506;0.7955801104972375   ;Fast 

201409  ;229972        ;0.5505;0.8121546961325967   ;Medium

201406  ;229940        ;0.5504;0.8176795580110497   ;Medium

200806  ;229872        ;0.5503;0.8232044198895028   ;Medium

201209  ;229796        ;0.5501;0.8287292817679558   ;Medium

[...]

201607  ;180380        ;0.4318;0.9447513812154696   ;Medium

201606  ;120685        ;0.2889;0.9502762430939227   ;Slow 

201603  ;85959         ;0.2057;0.9558011049723757   ;Slow 

[...]

199504  ;1             ;0     ;0.9834254143646409   ;Slow

Took a little less than 5s to finish the results for a 4M record table on SP12. I didn't spent too much time looking at resource consumption for this query but it might be a greedy one.

I tried to think on the graphical way of doing that but so far could not think of anything easy.

I suggest you to check WITH clause and other window functions that might be worth using here.

ps.: I guess you can use SUM as window function as well and re-use one of the other window functions here possibly subquerying your way to one single query. Haven't spent much time on it though.

BRs,

Lucas de Oliveira

lucas_oliveira
Advisor
Advisor
0 Kudos

Well actually you can lower that down to a single (&sad) select SQL by using SUM as a window function as well:


select

  CALMONTH,

  INDIVIDUAL_CNT,

  TOTAL,

  PERCENTAGE,

  RANK,

  (CASE WHEN RANK<=0.8 THEN 'Fast'

        WHEN RANK > 0.8 and RANK <= 0.95  THEN 'Medium'

           ELSE 'Slow'

     END) as RANK_STR

from (select

        CALMONTH,

        INDIVIDUAL_CNT,

        TOTAL,

        round(to_decimal(INDIVIDUAL_CNT/total)*100, 4) PERCENTAGE,

        percent_rank() over (order by round(to_decimal(INDIVIDUAL_CNT/total)*100,4) desc) as rank

  from (select

               CALMONTH,

                 INDIVIDUAL_CNT,

             SUM(INDIVIDUAL_CNT) over () as total

             from (

                   select left(replace(FLDATE,'-',''), 6) as calmonth,

                          count(BOOKID) as individual_cnt from SAPABAP1.SBOOK

             group by left(replace(FLDATE,'-',''),6))))

Performance was actually good (considering I'm using a single small table and not a complex view): total execution of 3.8s with 2.9s on JECalculate (replace and left function on the innermost select query). So if you don't have to calculate anything it should be wa(a)aay faster. It also consumed 3.6GB to execute it in my case:

ps.: Still haven't tried with WITH clause.

BRs,

Lucas de Oliveira

lucas_oliveira
Advisor
Advisor
0 Kudos

You can abuse window functions a bit more and actually lower that down to the following:


select *, (CASE

           WHEN RANK_100 <=80.00 THEN 'Fast'

        WHEN RANK_100 > 80.00 and RANK_100 <= 95.00 THEN 'Medium'

           ELSE 'Slow'

        END) as RANK_STR from (

select

  FLDATE,

  count(BOOKID) as individual_cnt,

  sum(count(BOOKID)) over() as total_cnt,

  round(to_decimal((count(BOOKID)/sum(count(BOOKID)) over())*100),2) as contrib,

  percent_rank() over(order by count(BOOKID) desc) as rank,

  round(percent_rank() over(order by count(BOOKID) desc)*100,1,ROUND_HALF_UP) as rank_100

from SAPABAP1.SBOOK

group by FLDATE)

(I saved the CALMONTH thing this time for the sake of simplicity)

BRs,

Lucas de Oliveira

former_member224444
Participant
0 Kudos

Hi Lucas,

Thanks a lot for your effort and time on this. I think you have understood this scenario in different way. Please check the below example

I don't want to check individual percentage of a Part number. Cumulatively if parts percentage sum which gives first 80% then it is FAST,  Cumulatively next 15% of sum is Medium, And next 5% is SLOW.  That is why i want percentage of previous record to be added to current record till it reaches first 80 percentage, then it should be cleared to sum next 15% sum. Business want to see group of parts which contributes to FAST, Medium and SLOW. 

Please let me know if you are clear with this example and let me know the possible way to do it if Cursor is not the good option.

Thanks,

Rithika