Min and Max Data Column

Hello,


I'm wondering if there's a way to do this without creating a SQL View.


Here's an example.

I want to create two additional columns in the data. One column that displays the data based on the smallest date ID, and the other column based on the largest date ID.


Here's how I want it to look (before and after)


Any ideas?


Thanks in advance!



Just do it in your SQL Statement, I would the change that datacube to Wharehoused storage. Or make a view.


There might be a way with Calculated element or an Aggregate in the data cube.


You can create this in the data cube layer with a number of aggregate and join transforms. The idea is to first aggreagte just the Store ID and Date ID to get the minimum (smallest) date ID by store ID and do the same for the maximum (laregst) date ID. The aggregate transfrom should have the store ID set to be grouped by and the Date ID to aggregate to Min/Max. Then join each with the original dataset using the Store ID and Date ID as the keys for the (inner) join. Then join the min and max branches together using only the store ID as the key (inner join) and without keeping the date ID. You should get a table with the store ID, the min and the max values (make sure you rename the min and max columns under the edit output elements before you join them. Finally, you can join that again to the original source and again have the store ID as the key. Here you should get a table looking like the one in your screen shot. Here is what the final data cube should look like: