connect demand table with supply table

arieltopaz — Thu, 20 Feb 2020 15:24:17 GMT

Hi all

I trying to connect demand table with supply table

the problem starts with the differences between dates

demands vs supply

I will like to get the coverage date from the supply the cover all the demand qty

see example

this is what I got

mrp_part	mrp_due_date	mrp_type	Work_Order_Number	mrp_qty	Inv	mrp_balance	Qty_Part_per_DD
CAA45-123	04-07-2019	DEMAND	307	1	1	0
CAA45-123	07-07-2019	DEMAND	308	1	1	-1
CAA45-123	11-07-2019	DEMAND	309	1	1	-2
CAA45-123	14-07-2019	DEMAND	310	1	1	-3
CAA45-123	18-07-2019	DEMAND	311	1	1	-4
CAA45-123	24-07-2019	DEMAND	312	1	1	-5
CAA45-123	25-07-2019	SUPPLY					2
CAA45-123	28-07-2019	SUPPLY					1
CAA45-123	31-07-2019	DEMAND	313	1	1

this is what I want to get

mrp_part	mrp_due_date	mrp_type	machine number	mrp_qty	Inv	mrp_balance	Qty_Part_per_DD	Cover Date
CAA45-123	04-07-2019	DEMAND	307	1	1	0
CAA45-123	07-07-2019	DEMAND	308	1	1	-1		25-07-2019
CAA45-123	11-07-2019	DEMAND	309	1	1	-2		25-07-2019
CAA45-123	14-07-2019	DEMAND	310	1	1	-3		28-07-2019
CAA45-123	18-07-2019	DEMAND	311	1	1	-4
CAA45-123	24-07-2019	DEMAND	312	1	1	-5
CAA45-123	25-07-2019	SUPPLY					2
CAA45-123	28-07-2019	SUPPLY					1
CAA45-123	31-07-2019	DEMAND	313	1	1

the cover date is the supply date that covers 100% of the demand (part for machine number)

inv - is the inventory today that covers the part for the first machine (307)

in this example, every machine need 1 qty of this item, but it is not always like that

mrp_balance is the qty in shortage for current demands

Qty_Part_per_DD - is the qty I get in the supply ( the date for the supply is shown in the field mrp_due_date)

no matter what I tried I cannot find the logic to do that

ill be appreciate your fast help

Thanks

Re: connect demand table with supply table

Brett_Bleess — Wed, 25 Mar 2020 19:46:44 GMT

I believe the first thing you likely need is one of the following topics from the Design Blog:

https://community.qlik.com/t5/Qlik-Design-Blog/The-Master-Calendar/ba-p/1471527

https://community.qlik.com/t5/Qlik-Design-Blog/Canonical-Date/ba-p/1463578

Here is the base URL to the Design Blog area you can use to further search if the above gets you on the right track but that leads to further questions...

https://community.qlik.com/t5/Qlik-Design-Blog/bg-p/qlik-design-blog

Regards,
Brett

topic connect demand table with supply table in QlikView

connect demand table with supply table

Re: connect demand table with supply table