How to get the last value for a measure based on datetimestamp

[Former Member]

I have an analytic view which has data from a foundation fact table and is joined with other attribute views. Now this fact table has multiple records for a given combination of dimensions (each different by datetimestamp). In the calculation view, how do I get only the last value of a given measure (based on datetimestamp) before I do the aggregation along dimensions/hierarchy? I believe I will have to define this computation in the projection before doing a union?

[Former Member · Accepted solution]

Would greatly appreciate a response for the above. Let me clarify with more details.Created an analytical based off a fact table. Each row in the analytical view represents inventory reported for a given ITEM at a given location maintained for a given vendor.

Each such combination of (CUST_SITE_NAME, CUST_ITEM_NAME, SUPPLIER_NAME is represented by a COLLAB_ID. Now for each such combination multiple values of inventory may exist (based on point_in_time_dt) and let's assume inventory is reported daily.

Now I have defined a calc view that points to this analytical view and somehow I need to get the most recent inventory based on the POINT_IN_TIME_DT. In this example I want to get the inventory value of 1600 reported on 3-26 for collab_id 27 even though multiple values could exist for the same collab_id before this date.  Here is the script (which I know is not right and need help).

SQLA_VIEW = CE_OLAP_VIEW

("_SYS_BIC"."e2sc-hana/AV_TEST_INV",

["COLLAB_ID","PIT_VALUE","DATETIMESTAMP", "CUST_SITE_NAME","CUST_ITEM_NAME","SUPPLIER_NAME"]);

SQLB_VIEW = CE_AGGREGATION

(:SQLA_VIEW,

["PIT_VALUE" AS "INVENTORY", MAX("DATETIMESTAMP") AS "DATE"],

["COLLAB_ID","CUST_SITE_NAME","CUST_ITEM_NAME","SUPPLIER_NAME"]

);

>> Notice I want the latest value for a given COLLAB_ID and then I want to drop the supplier and aggregate inventory at the CUST_ITEM_NAME, CUST_SITE_NAME level.

var_out= CE_AGGREGATION

(:SQLB_VIEW,

["INVENTORY","DATE","CUST_SITE_NAME","CUST_ITEM_NAME"]);

[Former Member · Accepted solution]

Hi,

I'll explain a solution for a similar scenario which I think corresponds to yours. Hopefully it helps.

1) Assume you have data from one table with key fields K1, K2, K3 where K3 is a date or a timestamp.

2) Assume you have one measure called M1.

You want to get the measure for the beginning or the end of a certain time period. Given the key fields above, K1 and K2 could have same values for different records and only differ by K3. Given this data structure, there's no straightforward aggregation to get the first or last measure.

Solution:

1) SELECT K1, K2, MAX(K3) FROM MY_TABLE GROUP BY K1, K2

2) INNER JOIN the results of this view to your original table, and you'll 'recover' the latest measure.

I'm not saying this is the best solution, but it is one I've used before. Attached is a code snippets from a solution I built which need to get automated gas tank measurements from beginning of each day. (Would copy and paste but can't stand the formatting issues of web-based editors)

recover measures at T1.txt.zip

[Former Member · Accepted solution]

Thanks a lot Jody. That example helped a lot. We were able to use that example to fine tune our CV to get the max(DATETIMESTAMP) for a given combination of K1,K2,K3 and then do an inner join and then do an aggregation for K1,K2.

[Former Member · Accepted solution]

Glad I could help!