I am building a Bill of Material (BOM) file. I have pre-processed the data to the point were each BOM component now has a Parent ID product key with carton size appended to it.
I would like to update BOM Qty field based on the Size Configuration Qtys for each
component which is stored on the file as shown above.
What is the best node to use. I have hit a brick wall trying to shuffle the BOM quantities based on the Product Carton Size configuration
Output Data would like below
Thank you @rfeigel for looking into the issue. sample data.xlsx (11.7 KB)
Attached the sample data in Excel ( Both Input and expected Output). I would like to update CQTY field with Values based on CQTY (Carton Size) against the parent ID. For Size XL data repeats in the example for CQTYL and CQTYXL (highlighted in yellow) which is valid.
Thanks
Sunil
Hi @rfeigel
Column Headers Meaning
Carton -Sizes
XS- Xtra Small, S-Small, M-Medium, L-Large, HM-Home & XL-Xtra Large
Using the first component, create parent BOM id. with the Size tagged on to it, if there is a value present in the Size Qty. In this example there are No XS & S carton configuration
Read each component and shuffle the BOM Size qty into CQTY based on Parent BOM Id Size to create the Carton configuration.
Hope this helps.
Thanks
Sunil
It splits out the product size code and gets the sort order by joining to another table.
As your quantity values are tabulated (pivoted) it unpivots to turn these back into individual rows, and then keeps only the row where the qty name (from the original qty column name) ends with the same code as the desired product code, so it can then return the table you requested. At the end, I have added an empty line after each group (parent product) but that is just for display purposes and can be ignored/skipped.
