Any alternative to RANK function in DWC

[former_member89645]

Dear Colleagues,

I have a table in DWC as below

Sample CSV attached.

I need to find out sum of amount for top 2 Suppliers for each category. The desired output is as below

I don't see any way of doing this is graphical view. Can it be done in SQL view? I tried using rank function but looks like rank is not supported in DWC quite yet. Refer this help link Not all HANA SQL functions are supported in DWC.

Any help to accomplish this in DWC or even in SAC is really appreciated.

Thanks!

supplier-rank-datacsv.txt

[Sven_Knöpfler · Accepted solution]

You can change the language of your SQL Script View to "SQL Script (Table Function"), then the rank function will be supported.

[karenave · Accepted solution]

sven.knpfler4

Hi, I currently have an issue in DWC because the tables do not have upsert or overwrite behavior. I need to control the data that reaches me duplicates to a master data and I thought to add a consecutive by Table function an autonumeric or make a cursor or click it in such a way that I can do control delvorver at least the maximum record based on it is numeric but I have not made it work.

You can tell me where to find documentation of the supported Syntax on Table Function in DWC and the same for SQL SCRIPT because it tries to make a row_number and apparently it does not support the sentences. And without documentation I have the doubt if my code is wrong or is an error in the tool

[Sven_Knöpfler · Accepted solution]

karenave

Hi Karen,

you find the syntax here: https://help.sap.com/viewer/c1d3f60099654ecfb3fe36ac93c121bb/2020_04_QRC/en-US/20a353327519101495dfd...

I would suggest that you create a DB user for that space and try to find the right sql command in the DB explorer, as it is much more comfortable to use that the sql script editor in the DWC where you have to define the datatypes of all the result columns manually.

https://help.sap.com/viewer/a2cea64fa3ac4f90a52405d07600047b/cloud/en-US/7fa981c8f1b44196b243faeb4af...

Hope it helps.

Kind regards

Sven

[Sven_Knöpfler]

Happy to help and that it worked!

[former_member89645]

Sven, That worked! thank you so much.

[j_vandersterren]

Hi Rajesh,

Could you please share your solution?

Regards,

Jos.

[Omika]

Hi, the link that sven has provided is not working, do you still have it?

[Omika]

Hi Sven these link are not opening, can you send again?