topic Re: Need help to make aggr() expression work with Valuelist using island table in QlikView

Need help to make aggr() expression work with Valuelist using island table

SujanJoeJacob — Fri, 12 Mar 2021 14:28:41 GMT

Hi all,
I'm trying to use aggr() expression in valuelist().
I know aggr() doesn’t seem to work in combination with ValueList() due to “grain mismatch”. And need to use island table.

But facing difficulties in creating one and understand how island table works.

Front End Table

Dimension:
KPI_Dimension:
Load * INLINE [
KPI_Metric
GMV
Discounts
Cancellation
Return
NetSales
];

Expression:

Pick(Match(ValueList('GMV', 'Discounts', 'Cancellation','Return','Net Sales'),
'GMV', 'Discounts', 'Cancellation','Return','Net Sales'),

Sum(Aggr(Only(line_items_price), [order_id])),

Sum(Aggr(Only(total_discounts), [order_id])),

Sum(Aggr(Only({<fulfilment_flag = {"unfulfilled"}, cancelled_flag = {"Yes"}>} line_items_price ), [order_id])),

Sum(Aggr(Only({<fulfillment_status = {"fulfilled"}, cancelled_flag = {"Yes"}>} line_items_price ), [order_id]))

......

)

Need help on the approach to create island table.

Re: Need help to make aggr() expression work with Valuelist using island table

Saravanan_Desingh — Fri, 12 Mar 2021 14:47:42 GMT

I think, if you use Aggr along with Pick & Match, it will give wrong results. May be try avoiding it.

Re: Need help to make aggr() expression work with Valuelist using island table

SujanJoeJacob — Fri, 12 Mar 2021 20:31:58 GMT

What's the alternative for > Sum(Aggr(Only(line_items_price), [order_id]))?
To get a sum grouped on dimension.

Re: Need help to make aggr() expression work with Valuelist using island table

MayilVahanan — Sat, 13 Mar 2021 03:05:20 GMT

Hi Sujan

Sum(Aggr(Only(line_items_price), [order_id]))

Based on my understanding, one order has many order item & each item has their own item price. If its the case, you need to use sum(line_items_price) instead of only(). can you try like below?

Sum(Aggr(Sum(line_items_price), [order_id]))

Re: Need help to make aggr() expression work with Valuelist using island table

SujanJoeJacob — Sat, 13 Mar 2021 17:39:42 GMT

Hey Mayil ,
Thanks for your reply.

Yes, that's problem with our schema.
Unfortunately, fields like discounts, subtotal are stored on order level and not line item level.

Hence have to do grouping and aggr() on order_id.
Any alternative to group without aggr()?

Re: Need help to make aggr() expression work with Valuelist using island table

MayilVahanan — Mon, 15 Mar 2021 03:55:56 GMT

Hi,

For Discounts & SubTotal, you can use Sum() function alone without Aggr.

For line_items_price, if you want to use aggr by Order Id based on ur requirement, try like below

Sum(Aggr(Sum(line_items_price), [order_id]))