Newbie Grouping Question

CloverDX Platform
1

I’m new to Clover and am at a lost as to how to attack this scenario. I have a flat file containing Products that I need to break down and insert or update into two DB tables (Product and ProductVariants). A “Product” would represent the high level product (think a tshirt style) and a “ProductVariant” would represent a specific type of that product (that same tshirt but in a specifc size). So I need to group the data in the master file by style and insert/update a Product table with that data, then I need to create a ProductVariant record containing the detailed product information. Hopefully that makes sense, but a simplistic schema is below to illustrate…

Flat File:
Style|Description|Size
ABCD|TShirt|Small
ABCD|TShirt|Medium
EFGH|TShirt|Small
EFGH|TShirt|Medium

Product Table:
ProductID|Style|Description
1|ABCD|TShirt
2|EFGH|TShirt

Product Variant Table:
ProductID|Style|Size|Color
1|ABCD|Small
1|ABCD|Medium
2|EFGH|Small
2|EFGH|Medium

2

I would do the following:

  1. Sort the input by Style.
  2. Add an ID to each style. This can be done in Reformat where you maintain two global variables, one for the current ID and one for the current style. If the style changes when compared to the previous record, the ID is increased by one and it remains the same until the next change of style.
  3. Send ProductID, Style and Description through the first output port of the Reformat and get rid of duplicates using Dedup component.
  4. Send ProductID, Size and Color through the second output port of the Reformat. Information about style is redundant here, I would skip it in this flow.
  5. Both flows continue to their own DBOutputTable which saves the records into a DB table.

I hope the description is clear but do not hesitate to ask any follow up questions.

3

Thank you for the direction. I’m a little confused on step #2, can you explain a little further?

4

Something like this:

string style = "";
integer productId = 0;

function integer transform() {
	if ($in.0.Style != style) {
		style = $in.0.Style;
		productId++; 
	}
	
	$out.0.ProductID = productId;
	$out.0.Style = style;
	$out.0.Description = $in.0.Description;
	
	$out.1.ProductID = productId;
	$out.1.Size = $in.0.Size;
	$out.1.Color = $in.0.Color;
	
	return ALL;
}