Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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