Generate a number based on distinct values in Calculation view

[former_member293952]

Hello Team,

I have a requirement as below. Could you please suggest on this?

I have a data as below. I have two column EAN11 and Material. Based on EAN11 distinct value need to generate and ID as show below. I would need to implement this in HANA calculation view. Aggregation count and counter giving individual row count but not based on EAN11. Appreciate if some one can help me on this ASAP.

ENA11		Material		Generate ID as 
10036000096054	100960510		1
10036000096054	100960520		1
10036000096054	12001617		1


9556204021121	100007533		2
9556204021121	100007534		2
9556204021121	100007535		2
9556204021121	100007536		2
9556204021121	100007538		2


9556204021122	100007537		3
9556204021122	100007539		3
9556204021122	100007540		3

[former_member208449]

Hi

If only the unique values of EAN11 field need to be given an ID,

1) Take the EAN11 field into a projection node and create a calculated column with default value as 1

2) Use an aggregation node to get unique values from EAN11

3) Give 2) to RANK node as input using partition by on calculated column. Also enable the rank column that generates the required ID

4) Do self join again with the table having the material field to get all the data

[pfefferf]

You have at least two options to reach your goal.

1) In a pure graphical way:
Create a Rank Node in your calculation view, which partitions by your ENA11 column and generates a rank column for the output. The result of the rank node can be joined via the ENA11 column to your original data, so that you can combine the original data with the rank column generated by the rank node.

2) In a scripted way:
You can use a user defined table function as data source in your calculation way. Within that table function you can use the RANK window function to produce the rank/ID column.

Regards,
Florian