Simulate missing figures

[Anonymous]

I'm throwing this one out to the smart people in the QlikView community because I'm not even sure if the following is possible.  We have a report showing sales orders, the items themselves are broken into packs of 25, 5 and 1.  The requirement is to show how many packs where used to fulfill the order, we don't have this data to import, just the order and quantity ordered so we need to simulate this information.

For example the following orders:

Order Number      Qty Ordered

1234                          127

1235                          200

1236                          66

Needs to be shown as the following:

Order Number      25 Pack Qty      5 Pack Qty      1 Pack Qty      Qty Ordered

1234                          4                       5                     2                     127

1235                          8                       0                     0                     200

1236                          2                       3                     1                     66

Over to you smart people!  Any suggestions would be greatly appreciated.

[#2 · Accepted solution]

Attached with answer.

Thanks

AJ

[Nicole-Smith]

Load script like this should do the trick:

Data:

LOAD *, [Qty Ordered]-[25 Pack Quantity]*25-[5 Pack Quantity]*5 as [1 Pack Quantity];

LOAD *, floor(([Qty Ordered]-[25 Pack Quantity]*25)/5) as [5 Pack Quantity];

LOAD *, floor([Qty Ordered]/25) as [25 Pack Quantity];

LOAD * INLINE [

    Order Number,Qty Ordered   

    1234,127   

    1235,200   

    1236,66

];

Example file is also attached.

[Anonymous]

That was quick, its just a pity I can't mark both answers as correct.

Thank you very much for your help!

[Nicole-Smith]

Ajay Prabhakaran beat me to it fair and square

[#6]

Lol. Even the time we replied back are the same 3:50 pm.