How to shuffle BOM quantities based on Product Carton Size Configuration

Hi Team,
Input File

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

Appreciate your help.

Could you please upload some sample data and provide descriptions of the column headers?

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.

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.

Hi @rfeigel ,
Hope the sample data and explanation provided is adequate. Please let me know if you need any further clarification.

I haven’t forgotten you. I’m really tied up right now. Maybe someone else can weigh in although I’ll get back to it when I have time.

1 Like

Hi @Suniljc ,

try this
BOM Parent unpivot and sort.knwf (104.2 KB)

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.


Thank you very much for the solution @takbb. I was away on holiday for Easter so the slow response.

1 Like

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.