Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Selective Aggr

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

qv1.png

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.

1 Solution

Accepted Solutions
sunny_talwar

Try this

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

Capture.PNG

View solution in original post

7 Replies
Gysbert_Wassenaar

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


talk is cheap, supply exceeds demand
sunny_talwar

Try this

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

Capture.PNG

Anonymous
Not applicable
Author

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

Anonymous
Not applicable
Author

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

Anonymous
Not applicable
Author

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.

sunny_talwar

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

Anonymous
Not applicable
Author

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