Order vs Receipt Details in Pivot Table - Qlik Community

Report Inappropriate Content · ‎2014-06-05

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

Report Inappropriate Content · ‎2014-06-06

Hi Aman,

find the attachment, hope this will help you

-- Rajendra

senpradip007 · ‎2014-06-06

PFA.

adarsh_a · ‎2014-06-06

PFA

Report Inappropriate Content · ‎2014-06-06

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 · ‎2014-06-06

Please find updated app. Hope it meets your target.

Report Inappropriate Content · ‎2014-06-06

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.

Report Inappropriate Content · ‎2014-06-06

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 · ‎2014-06-06

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

Order quantity: Sum([Order Qty])

Receivedquantity: Sum([Rec Qty])

Report Inappropriate Content · ‎2014-06-06

nope