Create a set of orders based on order box size

[markgraham123]

Hi all,

Can someone please help?

I'm working on a requirement where i have to create a set of orders sorting data set from A to B(Orders column) in my Sample data set.

I want to create a shopping cart by allocating orders accounting to total size of 25.

Cart has 10 slots. Each slot can hold size of 2.5 utmost. So, my order set total size should be 25 (2.5*10).

Attached is the QV application and attached image depicts the desired O/P.

Can someone please help? Any help is highly appreciated.

[sunny_talwar]

Having thought about this a little more, I don't think this is doable.

[markgraham123]

I did something similar to this before using Rangesum like below. But was struck with this. 😞

If(Pair = Previous(Pair) and Time = Previous(Time) and Rangesum(SlotSize, Peek('Ind_Slots')) < 3, Rangesum(SlotSize, Peek('Ind_Slots')) ,SlotSize) as Ind_Slots,

[markgraham123]

First we need to start filling every single slot with max capacity of 2.5 till we reach total capacity of 50.

[Brett_Bleess]

Mark, best I have in addition to what Sunny tried to do is to point you out to the Design Blog area to see if you might be able to find a post or two out there to give you some additional ideas on how to approach things:

https://community.qlik.com/t5/Qlik-Design-Blog/bg-p/qlik-design-blog

Sorry I do not have anything better.  If you did figure something out, please consider circling back to post that and then use the Accept as Solution button to mark that as the solution...

Regards,
Brett

[Saravanan_Desingh]

One solution is.

tab1:
LOAD *, If(Size>=2, CI, BI) As [Size of 10]
;
LOAD *, If(Peek('TS')>23 And RangeSum(Size, Peek('BS'))>2.5
	  		,If(Size<2, If(RangeSum(Size, 0)>2.5, Size, RangeSum(Size, 0)), 0)
	  		,If(Size<2, If(RangeSum(Size, Peek('BS'))>2.5, Size, RangeSum(Size, Peek('BS'))), If(Peek('BS')>=2,0,Peek('BS')))
	  	) As BS
	  , If(Peek('TS')>23 And RangeSum(Size, Peek('BS'))>2.5	  	
	  		,If(Size<2, If(If(Size<2, If(RangeSum(Size, 0)>2.5, Size, RangeSum(Size, 0)), 0)<2, RangeSum(0,1), 0), 0) 
	  		,If(Size<2, If(If(Size<2, If(RangeSum(Size, Peek('BS'))>2.5, Size, RangeSum(Size, Peek('BS'))), If(Peek('BS')>=2,0,Peek('BS')))<2, If(Not IsNull(Peek('CI')) And Peek('CI')> Peek('BI'),RangeSum(Peek('CI'),1),RangeSum(Peek('BI'),1)), Peek('BI')), Peek('BI')) 	  		
	  	) As BI	
	  , If(Peek('TS')>23 And RangeSum(Size, Peek('BS'))>2.5	  
	  		,If(Size>=2, 1, 0) 
	  		,If(Size>=2, If(Not IsNull(Peek('CI')) And Peek('CI')> Peek('BI'),RangeSum(Peek('CI'),1),RangeSum(Peek('BI'),1)), Peek('CI')) 	  		
	    ) As CI
;
LOAD *, If(Peek('TS')>=23, RangeSum(Size, 0), RangeSum(Size, Peek('TS'))) As TS
;
LOAD * INLINE [
    Order, Size
    A, 1
    B, 1
    C, 1.5
    D, 2.5
    E, 2.5
    F, 2.5
    G, 2.5
    H, 1
    I, 2.5
    J, 2.5
    K, 2.5
    L, 1.5
    M, 2.5
    N, 2.5
    O, 2.5
    P, 2.5
    Q, 2.5
    R, 1
    S, 1
    T, 1.5
    U, 2.5
    V, 2.5
    W, 1
    X, 1.5
    Y, 2.5
    Z, 2.5
];

Drop Field TS, BS, BI, CI;

[Saravanan_Desingh]

Simplified further.

tab1:
LOAD *, If(Size>=2, CI, BI) As [Size of 10]
;
LOAD *, If(If(Peek('CI')>Peek('BI'), Peek('CI'), Peek('BI'))=10 And RangeSum(Size, Peek('BS'))>2.5
	  		,If(Size<2, If(RangeSum(Size, 0)>2.5, Size, RangeSum(Size, 0)), 0)
	  		,If(Size<2, If(RangeSum(Size, Peek('BS'))>2.5, Size, RangeSum(Size, Peek('BS'))), If(Peek('BS')>=2,0,Peek('BS')))
	  	) As BS
	  , If(If(Peek('CI')>Peek('BI'), Peek('CI'), Peek('BI'))=10 And RangeSum(Size, Peek('BS'))>2.5	  	  	
	  		,If(Size<2, If(If(Size<2, If(RangeSum(Size, 0)>2.5, Size, RangeSum(Size, 0)), 0)<2, RangeSum(0,1), 0), 0) 
	  		,If(Size<2, If(If(Size<2, If(RangeSum(Size, Peek('BS'))>2.5, Size, RangeSum(Size, Peek('BS'))), If(Peek('BS')>=2,0,Peek('BS')))<2, If(Not IsNull(Peek('CI')) And Peek('CI')> Peek('BI'),RangeSum(Peek('CI'),1),RangeSum(Peek('BI'),1)), Peek('BI')), Peek('BI')) 	  		
	  	) As BI	
	  , If(If(Peek('CI')>Peek('BI'), Peek('CI'), Peek('BI'))=10 And RangeSum(Size, Peek('BS'))>2.5	  
	  		,If(Size>=2, 1, 0) 
	  		,If(Size>=2, If(Not IsNull(Peek('CI')) And Peek('CI')> Peek('BI'),RangeSum(Peek('CI'),1),RangeSum(Peek('BI'),1)), Peek('CI')) 	  		
	    ) As CI
;
LOAD * INLINE [
    Order, Size
    A, 1
    B, 1
    C, 1.5
    D, 2.5
    E, 2.5
    F, 2.5
    G, 2.5
    H, 1
    I, 2.5
    J, 2.5
    K, 2.5
    L, 1.5
    M, 2.5
    N, 2.5
    O, 2.5
    P, 2.5
    Q, 2.5
    R, 1
    S, 1
    T, 1.5
    U, 2.5
    V, 2.5
    W, 1
    X, 1.5
    Y, 2.5
    Z, 2.5
];

Drop Field BS, BI, CI;

[Saravanan_Desingh]

More simplified.

SET vAddCIorBI	=If(Not IsNull(Peek('CI')) And Peek('CI')> Peek('BI'),RangeSum(Peek('CI'),1),RangeSum(Peek('BI'),1));
SET vOldCIorBI	=If(Peek('CI')>Peek('BI'), Peek('CI'), Peek('BI'));
SET vSumSize	=If(RangeSum(Size, $1)>2.5, Size, RangeSum(Size, $1));
SET vGetBS		=If(Peek('BS')>=2,0,Peek('BS'));

tab1:
LOAD *, If(Size>=2, CI, BI) As [Size of 10]
;
LOAD *, If($(vOldCIorBI)=10 And RangeSum(Size, Peek('BS'))>2.5
	  		,If(Size<2, $(vSumSize(0)), 0)
	  		,If(Size<2, $(vSumSize(Peek('BS'))), $(vGetBS))
	  	) As BS
	  , If($(vOldCIorBI)=10 And RangeSum(Size, Peek('BS'))>2.5	  	  	
	  		,If(Size<2, If(If(Size<2, $(vSumSize(0)), 0)<2, RangeSum(0,1), 0), 0) 
	  		,If(Size<2, If(If(Size<2, $(vSumSize(Peek('BS'))), $(vGetBS))<2, $(vAddCIorBI), Peek('BI')), Peek('BI')) 	  		
	  	) As BI	
	  , If($(vOldCIorBI)=10 And RangeSum(Size, Peek('BS'))>2.5	  
	  		,If(Size<2, 0, 1) 
	  		,If(Size<2, Peek('CI'), $(vAddCIorBI)) 	  		
	    ) As CI
;
LOAD * INLINE [
    Order, Size
    A, 1
    B, 1
    C, 1.5
    D, 2.5
    E, 2.5
    F, 2.5
    G, 2.5
    H, 1
    I, 2.5
    J, 2.5
    K, 2.5
    L, 1.5
    M, 2.5
    N, 2.5
    O, 2.5
    P, 2.5
    Q, 2.5
    R, 1
    S, 1
    T, 1.5
    U, 2.5
    V, 2.5
    W, 1
    X, 1.5
    Y, 2.5
    Z, 2.5
];

Drop Field BS, BI, CI;