How to recreate store lookup based on an aggregated value

[ollien1]

Hi,

I'm trying to recreate a particular measure from a Java ActivePivot project in atoti. The Java code for this measure looks like this:

Copper.storeLookup(SHIPPERS)  
.withMapping(SHIPPERS_SHIPPER_NAME, Copper.level(ORDERS_SHIPPER_NAME))  
.withMapping(SHIPPERS_INTERVAL, Copper.measure("Quantity Sold").map(TrainingUtils::retrieveIntervalLabel))
.valuesOf(SHIPPERS_TRANSPORT_RATES)  
.map(r -> r.readDouble(0), Types.TYPE_DOUBLE)

The mapping function is a simple if/else block that returns a string based on the value of the "Quantity Sold" measure.

I've been trying to recreate this in atoti but so far haven't had much luck. A regular join here doesn't work as the interval value needs to be determined by the aggregated value of the "Quantity Sold" measure. Any advice on how to recreate this if it is possible?

Cheers!

[Skyscimitar]

Hi Ollie,
You can use the tt.where or the tt.switch functions to create measures requiring if/else logic. You could probably combine this with the tt.agg.single_value and tt.at functions to recreate the behavior you want.

If you need more help recreating the measure, could you share your datastore cube structure so we can advise you on the best way to create this measure.

FYI the lookup function is coming in the next version of atoti.

[ollien1]

Thanks Daniel,

I managed to figure it out though I'm not sure if it's the cleanest solution:

    quantity = m[TrainingCubeMeasures.QUANTITY_SOLD.value]
    less_than_100 = tt.where((quantity > 0) & (quantity < 100), 1)
    between_100_300 = tt.where((quantity >= 100) & (quantity < 300), 2)
    between_300_500 = tt.where((quantity >= 300) & (quantity < 500), 3)
    between_500_700 = tt.where((quantity >= 500) & (quantity < 700), 4)
    more_than_700 = tt.where(quantity >= 700, 5)
    interval = tt.switch(
        less_than_100 + between_100_300 + between_300_500 + between_500_700 + more_than_700,
        {
            1: "Quantity less than 100",
            2: "Quantity between 100 and 300",
            3: "Quantity between 300 and 500",
            4: "Quantity between 500 and 700",
            5: "Quantity more than 700",
        },
        default="Unknown"
    )
    rate = tt.agg.single_value(
        tt.where(
            interval == l[ShippersTableColumn.INTERVAL.value],
            m[TrainingCubeMeasures.TRANSPORT_RATES.value]
        ),
        scope=tt.OriginScope(l[ShippersTableColumn.INTERVAL.value])
    )
    m[TrainingCubeMeasures.TRANSPORT_COSTS.value] = tt.agg.sum(
        rate * m[TrainingCubeMeasures.PURCHASES.value],
        scope=tt.OriginScope(
            l[ProductsTableColumn.PRODUCT_NAME.value],
            l[OrdersTableColumn.SHIPPER_NAME.value]
        )
    )

Is there a better way to implement the interval measure? As far as I'm aware you can't use boolean conditions in the switch statement which is why I resorted to combining the multiple where conditions together (please correct me if I'm wrong about this).

Other than that I was being thrown off because the project I am trying to migrate had the interval hierarchy as a slicing hierarchy which was preventing taking this approach. It turns out this didn't strictly need to be slicing so changing it to a regular hierarchy meant doing things this way was possible.

I did originally try using the at function to get around the slicing hierarchy but using the value of the interval measure as the location to shift to didn't appear to work. Is this expected?

Other than that good to hear lookups will be available in the next release!

Thanks

[Skyscimitar]

With the current API this is probably the best implementation. The changes coming in the next API will allow you to use boolean conditions in the switch statement which should simplify the code a bit.

If you want you can install a continuous build of atoti instead of an official release which will give you access to the upcoming features.

This is normal behavior for the at function as well, you can only use levels or constants as target coordinates for the shift.