Comparing Pyrchace order with Packing List to find exceed quantity

[selvakumarsr]

Hi All,

I have two tables. One is Purchase order and other one is packing list like below

PO:

LOAD
[Po Number] &'#'& [Item Code]&'#'& [P.O. Position] as _#KEY,
[Po Number],
[ItemCode],
[P.O. Quantity],
[P.O. Position]
FROM
[..\PO\PO.xlsx]
(ooxml, embedded labels, table is Sheet1);

PL:

LOAD
[Po Number] &'#'& [ItemCode]&'#'& [PL P.O. Position] as _#KEY,

PLQTY,

PLNUMBER,

[PL P.O. Position]

FROM
[..\PL\PL.xlsx]
(ooxml, embedded labels, table is Sheet1);

Using this, im getting the below result

Po Number	Item Code	P.O. Position	PL Quantity	PO Quantity	PO VS PL	Exceeded/Not Exceeded
P011001	225	20	84.000	84.000	0.000	Not Exceeded
P011001	225	146	73.000	73.000	0.000	Not Exceeded
P011001	225	332	7.000	7.000	0.000	Not Exceeded
P011001	225	428	6.000	6.000	0.000	Not Exceeded

But my purchase order also has P.O Position 498 with PO quantity of 100 which is not delivered in the packing list (Its not available in PL table) which i need to display like below table.

Po Number	ItemCode	P.O. Position	PL Quantity	PO Quantity	PO VS PL	Exceeded/Not Exceeded
P011001	225	20	84.000	84.000	0.000	Not Exceeded
P011001	225	146	73.000	73.000	0.000	Not Exceeded
P011001	225	332	7.000	7.000	0.000	Not Exceeded
P011001	225	428	6.000	6.000	0.000	Not Exceeded
P011001	225	498	0.000	100.000	0.000	Not Exceeded

Is there a way to display in a table like above.

Thanks,

Selva

[QlikJunkie]

Consider Outer join...!!

https://www.tutorialspoint.com/qlikview/qlikview_joins.htm

[Brett_Bleess]

Selvakumar, it is your P.O. Position that is creating the issue, as that value only exists in one of the tables, so I do agree with the other poster, a join of some sort of the two tables would likely be the best solution here. 

Here is another Design Blog post that may be of some help to you:

https://community.qlik.com/t5/Qlik-Design-Blog/To-Join-or-not-to-Join/ba-p/1463102

If the other post did help you get what you needed, do not forget to come back and use the Accept as Solution button to mark the post which will give the poster credit for the help and let others know it did work for you.

Regards,
Brett