Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Attached with answer.
Thanks
AJ
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.
That was quick, its just a pity I can't mark both answers as correct.
Thank you very much for your help!
Ajay Prabhakaran beat me to it fair and square
Lol. Even the time we replied back are the same 3:50 pm.