Iteration formula help

[RedHeadedStepITChild]

I apologize if the subject was not concise enough, but was unsure how to word it. I also apologize if this question has been answered before, but after searching this forum quite extensively, I didn't find any posts that quite helped me out.

Background of the issue:

We have recently implemented a new ERP system for our furniture company with our go-live date of Jan 1. I am doing some trend analysis using the old ERP for historical actuals and the new ERP for YTD values. The old process was to sell and invoice a table top and legs as two separate finished goods SKUs. The new process is to combine those same part numbers into a sales-kit SKU. The problem is that the new ERP applies invoice dollars to the parent (sales-kit) SKU only so I can't get a 1 to 1 match to do analysis on.

What I've done and what I think needs to be done:

In the new database I'm using two tables: Part and PartMtl to get the parent SKU and its respective components. I created a formula to concatenate the components. I'm now working on a formula using the old database and one table from that database to iterate through the partMaster table and attempt to create the same string to match the results from the prior mentioned formula. It was my thinking if I can match the two strings, I can then mimic the sales kit methodology using the old system's data (and whatever else may need to be done).

Iteration psuedo formula:

stringvar firstPart := "";
stringvar secondPart := "";
stringvar complete := "";

while firstPart <> left({newSystemConcat},5)
do
(
next({partMaster.IPROD});
);
firstPart

while secondPart <> right({newSystemConcat},5)
do
(
next({partMaster.IPROD});
);
secondPart

complete = firstPart & secondPart


I do not get any results.

Here is a "graphical" illustration of the problem:

SKU Description QTY Sold Revenue
----------------------------------------------------------
22001-BLUBLU Blue Tbl w/ Blue Legs 200 $1,000


The SKU 22001-BLUBLU is made of of two components, parts 12345 and 09876. With that being said, the same data from the old system would look like:

SKU Description Qty Sold Revenue
-----------------------------------------------------------
12345 Blue table top 200 $750
09876 Blue legs 200 $250


To wrap up:

I'm basically trying to create a data set that translates the two parts from the old system that captures the revenue and quantity for both and creates a single record that matches the SKU in the new system. It was my thinking that I could do so by matching the strings as shown above.

I apologize for the length, but wanted to try to give as much information as possible for someone to help. I'd much appreciate help on the above formula or if someone suggests another route at tackling this problem -- shoot me your ideas.

Thanks much

Jason

 

[RedHeadedStepITChild]

oops...I left out in the do while portion

firstPart = next({partMaster.IPROD});
secondPart = next({partMaster.IPROD});

 

[fisheromacse]

What is method/system used to combine the part #'s into the sales kit part #?

For example, if i have 2 items that make up the sales kit, one with part # = 121212 and the second with part # = 212121, does the sales kit part # = 333333? and how do you prevent duplicate sales kit part #'s (ie: sales kit #2 has part #'s 111111 and 222222 would equal 333333 also)

 

[RedHeadedStepITChild]

Unfortunately there is not any rhyme or reason to the parent (sales kit) SKUs in regards to the components that make them up.

For instance the component part numbers of:
73868 (top)
67152 (legs)

create the sales kit 220015-BBLURE3072-TBLKBLK

don't beat me up over part numbers the powers that be didn't consult my opinion

 

[fisheromacse]

I completely understand not being consulted for an opinion and then asked to do the impossible as a result.
I do not see an way to do what you are asking without TONS of manual labor creating a lookup table just for translation...column1=kit part #, column2=part no1, column2=part no2, column3=part no3, etc.
That doesn't mean there isn't one, just that I am not seeing it.

 

[RedHeadedStepITChild]

Yes, I knew there would be a lot of work in creating look-up tables for translation. I'm half way considering doing this using SSIS and a ODS. However, the same principle for translation of the old system to the new applies using those tools as well.

However, I can't, for some reason, get any results on the formula that I posted.

It's Friday dang it! Grrrr