topic Re: Simulate missing figures in QlikView

Simulate missing figures

Anonymous — Thu, 27 Mar 2014 19:39:52 GMT

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.

Re: Simulate missing figures

Thu, 27 Mar 2014 19:50:51 GMT

Attached with answer.

Thanks

Re: Simulate missing figures

Nicole-Smith — Thu, 27 Mar 2014 19:50:57 GMT

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.

Re: Simulate missing figures

Anonymous — Thu, 27 Mar 2014 19:58:50 GMT

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

Thank you very much for your help!

Re: Simulate missing figures

Nicole-Smith — Thu, 27 Mar 2014 19:59:42 GMT

Ajay Prabhakaran beat me to it fair and square

Re: Simulate missing figures

Thu, 27 Mar 2014 20:03:29 GMT

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