lookup formula/grouping

[Tarashah76]

I have three columns column 1 column 2 and column3. column1 is coming from a mv, column 2 is coming from a business schema and column3 is a look up formula. 

I have filtered on column1 and column2 as you can see the values are same for 2 rows for these two columns. Now I want to create a new column that if for column1 and column2 we are getting column3 as "S" and "V" then final rows should be seen as "V", if all rows are only "S" then "S", and if all rows are only "V" then "V".

How can I achieve this? I tried applying case on the lookup, but it is again giving row wise result and not grouped by column1 and column2

[dylanwan · Accepted solution]

The lookup function is for looking up another lookup table using the key.

To get the value, we will need to aggregate across these two rows:

if(distinct(schemaX.tableN.column3)>1, "V", max(schemaX.tableN.column3))

To repeat the same value, we may need to use level based measure.

https://docs.incorta.com/6.0/concepts-level-based-measure