Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Announcements

Introducing Qlik Answers: A plug-and-play, Generative AI powered RAG solution.
**READ ALL ABOUT IT!**

- Qlik Community
- :
- All Forums
- :
- QlikView App Dev
- :
- inventory of a product based on the orders

Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Navars

Creator

2020-04-18
10:41 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

1,026 Views

2 Solutions

Accepted Solutions

kaushiknsolanki

Partner Ambassador/MVP

2020-04-18
02:37 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!

Navars

Creator

2020-04-28
07:49 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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)

)

928 Views

3 Replies

kaushiknsolanki

Partner Ambassador/MVP

2020-04-18
02:37 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!

Navars

Creator

2020-04-20
05:42 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

967 Views

Navars

Creator

2020-04-28
07:49 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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)

)

929 Views