Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QLIKWORLD LIVE! MAY 16 - 19TH, EARLY BIRD DISCOUNTS! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
Navars
Contributor III
Contributor III

inventory of a product based on the orders

Hi All,

I want to calculate the Inventory of the products based on its orders.

eg. in the source file, product P1 has total units has 30, its first order (Order id 1) has ordered 15 units so the remaining units of Prod P1 has 15, we call it as the order is completed. (30-15=15)

in the second-order  (Order id 2) it ordered 20 units, but we have 15 units only (15-20 =-5) partially completed.

in the third-order (Order id 3) it ordered 15 units, but we don't have units to give hence we canceled the order.

 

Thanks in advance.

Sra1

Labels (2)
2 Solutions

Accepted Solutions
kaushiknsolanki

Hi,

Try this code.

Data:
LOAD prodid,
ordid,
[noof orders],
[total units],
AutoNumber(RowNo(),prodid) as Rownum
FROM
[C:\Users\Kaushik\Downloads\inventory.xlsx]
(ooxml, embedded labels, table is Sheet1);


Left join (Data)
Load prodid, Sum([total units]) as [Overall Units]
Resident Data Group by prodid;

Data1:
Load *,If([Remaining Units]>0,'Completed',If([Remaining Units]<0,'Partial Completed','Cancelled')) as Status;
Load *,If(Rownum=1,[Overall Units]-[noof orders],If(peek([Remaining Units]) <=0,0,peek([Remaining Units])-[noof orders])) as [Remaining Units]
Resident Data Order by prodid,ordid;

Drop table Data;

 

Output is as shown below

Capture.JPG

 

Regards,
Kaushik
If reply is satisfactory, please "Like" the post.
If reply is solution, please mark as "Correct".

View solution in original post

Navars
Contributor III
Contributor III
Author

to get the correct statues i used below one.

if(remaining>0 or (remaining=0 and Above(remaining)=orders), 'order completed',

if(remaining<0 and Above(remaining)=0,'closed',

if(remaining<0, 'Partially completed',

)))

and i achieved the same output in frontend by using the below expression.

=if(aggr(RowNo(),prodid,ordid)=1, [units]-[orders],

if(aggr(RowNo(),prodid,ordid)>1,above(remaining)-orders)

)

View solution in original post

3 Replies
kaushiknsolanki

Hi,

Try this code.

Data:
LOAD prodid,
ordid,
[noof orders],
[total units],
AutoNumber(RowNo(),prodid) as Rownum
FROM
[C:\Users\Kaushik\Downloads\inventory.xlsx]
(ooxml, embedded labels, table is Sheet1);


Left join (Data)
Load prodid, Sum([total units]) as [Overall Units]
Resident Data Group by prodid;

Data1:
Load *,If([Remaining Units]>0,'Completed',If([Remaining Units]<0,'Partial Completed','Cancelled')) as Status;
Load *,If(Rownum=1,[Overall Units]-[noof orders],If(peek([Remaining Units]) <=0,0,peek([Remaining Units])-[noof orders])) as [Remaining Units]
Resident Data Order by prodid,ordid;

Drop table Data;

 

Output is as shown below

Capture.JPG

 

Regards,
Kaushik
If reply is satisfactory, please "Like" the post.
If reply is solution, please mark as "Correct".
Navars
Contributor III
Contributor III
Author

Hi Kaushik,

Thanks for the reply.

by using your logic I am getting the expected result. but in one case Status is not populating properly.

product P2 has total units 25 and order received 25, it supposed to show as completed status and remaining units as zero. but its showing Cancelled as the status.

I have attached the sample data, need to check the Product P2 for the above scenario.

 

Thanks,

Sra1

Navars
Contributor III
Contributor III
Author

to get the correct statues i used below one.

if(remaining>0 or (remaining=0 and Above(remaining)=orders), 'order completed',

if(remaining<0 and Above(remaining)=0,'closed',

if(remaining<0, 'Partially completed',

)))

and i achieved the same output in frontend by using the below expression.

=if(aggr(RowNo(),prodid,ordid)=1, [units]-[orders],

if(aggr(RowNo(),prodid,ordid)>1,above(remaining)-orders)

)