topic Re: Selective Aggr in QlikView

Selective Aggr

Anonymous — Mon, 04 Jun 2018 18:09:03 GMT

Hi!

I have 2 tables; one containing all my products, and one containing all the purchaseorders, when products are bought.

In the product table I have a return quantity, representing the quantity I want to return. My goal is for each product to return, to get the list of PO I must reference to cover the quantity.

To better show what I try to do let`s take this sample data:

Product:

load * inline [

ProductCode, QtyReturn

1 , 500

2 , 900

3 , 200

4 , 300

];

PurchaseOrder:

load * inline [

PO_Num, ProductCode, QtyBought, PO_Date

2234, 1 , 200, 2018-06-03

2235, 2 , 300, 2018-06-02

2234, 3 , 100, 2018-06-03

2234 ,4 , 500, 2018-06-03

3214 , 1 , 100, 2018-06-02

4564 , 1 , 200, 2018-05-22

3245, 2 , 2500, 2018-05-22

5645, 3, 200, 2018-05-22

4344, 1 , 200, 2018-04-03

];

With this formula I am able to get a list of all PO for each product and all quantities

=aggr(concat(PO_Num,', ', -PO_Date ),ProductCode)

Which for product 1 is : 2234, 3214, 4564, 4344

and

=aggr(concat(QtyBought,', ', -PO_Date ),ProductCode)

200, 100, 200, 200

What I really want is to get only the PurchaseOrder numbers until I reach the amount to return. QtyReturn = 500 for product 1.

So, for product 1, it would be : 2234, 3214, 4564 instead of 2234, 3214, 4564, 4344.

I tried many combination of Aggr, Sum, Rank ... But can`t seem to put my finger on the solution ...

Here is the last thing I tried, which give me just 2234 ... not even close.

= concat( IF( aggr ( rank( sum(QtyBought )),ProductCode)<=QtyReturn, PO_Num) , ';', -PO_Date )

Thanks for your help, hope I was able to explain clairly what I am trying to achieve.

Re: Selective Aggr

Gysbert_Wassenaar — Mon, 04 Jun 2018 18:13:04 GMT

You'll have to sort the aggr. That's complicated but, have a look at this blog post: Recipe for a Pareto Analysis – Revisited

Re: Selective Aggr

sunny_talwar — Mon, 04 Jun 2018 18:14:30 GMT

Try this

=Concat(Aggr(If(QtyReturn >= RangeSum(Above(Sum(QtyBought), 0, RowNo())), PO_Num), ProductCode, PO_Date), ', ', -PO_Date)

Re: Selective Aggr

Anonymous — Mon, 04 Jun 2018 18:16:16 GMT

I will go read the blog post to learn more ...

Re: Selective Aggr

Anonymous — Mon, 04 Jun 2018 18:16:56 GMT

That works ... thanks ... Will check it until I fully understand it.

Re: Selective Aggr

Anonymous — Mon, 04 Jun 2018 18:56:48 GMT

By checking more, it does not check for the dates correctly. Trying to get the most recent dates first. But if they are not already sorted, that does not seem to work. That is why I had the minus sign in front of PO_Date.

Re: Selective Aggr

sunny_talwar — Mon, 04 Jun 2018 18:59:56 GMT

Yes, if the dates are not sorted from the script... you have two options

1) If you have qv12 or above... you can use this

The sortable Aggr function is finally here!

=Concat(Aggr(If(QtyReturn >= RangeSum(Above(Sum(QtyBought), 0, RowNo())), PO_Num), ProductCode, (PO_Date, (NUMERIC))), ', ', -PO_Date)

2) Sort PO_Date in the script

Re: Selective Aggr

Anonymous — Mon, 04 Jun 2018 19:03:25 GMT

I am still at version 11 .. Will use option 2 ...