Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
Not applicable

Order vs Receipt Details in Pivot Table

Hi,

I am working on ' Inventory & Production' application for a client. There is a requirement to show details for order vs receipt in a pivot table. In this case two tables are there, one for Header and other one for line detail.

In header table, customer raise a PO for placing a order for required qty as a bulk order and in line table he receives qty on multiple times against supply.

For Ex;

Hear Table

----------------

Po No          PO Date          Order Qty           Vendor           Item

------------------------------------------------------------------------------------------------------

PO10245     01/05/2014          500               52104               ACB5044

PO12548     05/05/2014          700               84501               ACV5042    

And When customer receives supply against order, entries in Line Table is as follows:

Line Table

-------------

Po No          Supply Date          Rec Qty           Vendor           Item               GRN No

-----------------------------------------------------------------------------------------------------------------------------------

PO10245     08/05/2014               110               52104               ACB5044      GRN10104

PO10245     18/05/2014               99                 52104               ACB5044      GRN10143

PO10245     21/05/2014               111               52104               ACB5044      GRN11108

PO12548     15/05/2014               300               84501               ACV5042      GRN11110

PO12548     20/05/2014               100               84501               ACV5042      GRN11111

-------------------------------------------------------------------------------------------------------------------------------------

And output Required in following format in Pivot Table:

Vendor               Po No         Item           GrNNo          Order Qty          Received Qty

------------------------------------------------------------------------------------------------------------------------------------

Total                                                                          500                320

------------------------------------------------------------------------------------------------------------------------------------

52104             PO10245       ACB5044      GRN10104     500               110

                                                              GRN10143     500               99

                                                              GRN11108     500               111

----------------------------------------------------------------------------------------------------------------------------------

Total                                                                          700               400

----------------------------------------------------------------------------------------------------------------------------------

84501              PO12548      ACV5042      GRN11110    700               300              

                                                              GRN11111     700              100              

----------------------------------------------------------------------------------------------------------------------------------

Please suggest me a solution so that I can fulfill client requirement. 

Awaiting for your suggestions.(High Priority Requirement)

Thanks in Advance

Tags (2)
9 Replies
Not applicable

Re: Order vs Receipt Details in Pivot Table

Hi Aman,

find the attachment, hope this will help you

-- Rajendra

senpradip007
Valued Contributor III

Re: Order vs Receipt Details in Pivot Table

PFA.

adarsh_a
New Contributor III

Re: Order vs Receipt Details in Pivot Table

PFA

Not applicable

Re: Order vs Receipt Details in Pivot Table

Dear Pradip,

Thanks for providing valuable suggestion for same. But I think it will not work properly because if there will be another PO with Same Order Qty then sum(distinct Order Qty) will show wrong output.

If possible please provide more better and feasible solution.

Thanks Again

senpradip007
Valued Contributor III

Re: Re: Order vs Receipt Details in Pivot Table

Please find updated app. Hope it meets your target.

Not applicable

Re: Order vs Receipt Details in Pivot Table

Hi,

Because Order Qty of PO is a Measure, so will it better to keep this on Link table in Data Model.

If we can join these two tables and can achieve goal then it will much better for us.

Not applicable

Re: Order vs Receipt Details in Pivot Table

Hi,

Because Order Qty of PO is a Measure, so will it better to keep this on Link table in Data Model.

If we can join these two tables and can achieve goal then it will much better for us.

pradeepreddy
Valued Contributor II

Re: Order vs Receipt Details in Pivot Table

Try the bellow expressions along with the dimensions which you have taken

Order quantity:  Sum([Order Qty])

Receivedquantity: Sum([Rec Qty])

Not applicable

Re: Order vs Receipt Details in Pivot Table

nope