Data Services (BODS) - Is there a function to do DENSE RANK or STRING AGG?

[tkim1205]

Hello Data Service experts,

In HANA, there is a DENSE RANK and STRING AGG function.

I am trying to accomplish what these functions do in Data Services, but I am not able to find similar functions.

1. Does anyone know if there is an equivalent in Data Services?
2. Or will there be these functionalities in DS in the future?

Here is what the 2 functions do:

• STRING_AGG() - This function will aggregate strings based on specified partition and order.

Ex.

CUST | ORDER #

John | 1

John | 2

John | 5

John | 7

John | 11

If I do a STRING_AGG() over partition CUST, ordered by ORDER desc, I will get:

CUST | STRING_AGG

John | 1, 2, 5, 7, 11

• DENSE_RANK() - This function will output a rank based on specified partition and order.

Ex. TABLE:

CUST | PRODUCT | PURCHASE

John | A | $500

John | A | $1000

John | B | $2500

John | B | $1300

John | B | $7000

John | C | $5000

If i do a DENSE_RANK() over partition CUST & PRODUCT, ordered by PURCHASE descending, my outcome would be:

CUST | PRODUCT | PURCHASE | DENSE_RANK

John | A | $500 | 1

John | A | $1000 | 2

John | B | $2500 | 2

John | B | $1300 | 3

John | B | $7000 | 1

John | C | $5000 | 1

Thanks!

[former_member187605]

If you know the maximum of records per partition, you can use a Reverse_Pivot transform and then concatenate all your result columns together.

[rajan_burad]

Hi Anthony,

For the string_agg() you've to write a custom function in BODS.

For dense_rank() you can go with gen_row_num_by_group() function in BODS.

Let us know if you need more assistance.

Thanks,

Rajan