mapping multiple values into 1 category

[#1]

Hi guys,

I have a mapping of 3 product categories:

ProductTypes:

Mapping LOAD * INLINE [

Col1, Col2

1, Candy

2, Cake

3, Cookie];

I use that mapping when loading my product fact table "Products" as:

Products:

LOAD

ProductID,

ApplyMap('ProductTypes', ProductTypeID, 'Unkown') AS [Product Types]

...

FROM Products.qvd(qvd)

Question: How do I create an additional (artificial) product type that will combine "Cake" and "Cookie" into Baking?

I'm thinking of something like this:

ProductTypes:

Mapping LOAD * INLINE [

Col1, Col2

1, Candy

2, Cake

3, Cookie

2 or 3, Baking];

I need this in order to make ProductType a dimenssion and then display sum of products by product type as in table below:

ProductType, Number of products

Candy,   5

Cake,   3

Cookie,  6

Baking,   9

Total,   14

Thank you in advance!

[#2]

Bump. Please provide at least high level idea.

[sybase67]

Load 'Product' data  as is , then (ADD to same table ) >>>load only "Selected Product" data with "New Product ID".

In your "mapping" use "New Product ID" with name Baking ...

Hope it helps...

[sybase67]

Load 'Product' data  as is , then (ADD to same table ) >>>load only "Selected Product" data with "New Product ID".

In your "mapping" use "New Product ID" with name Baking ...

Hope it helps...

[#5]

Thank you sybase67.

While it is possible, the sample I provided in my question has symplified schema. In reality, my product id is a composite key that I autogenerate (RelationshipKey - is the Product ID in my sample):

Autonumber

( RelationshipKey&','& VolPartKey&','& ChildPartKey&','& AgencyID&','& MatchKey&','&LocationKey) as

%Key_FactMatchTrue

Is there another way to acompish this without doubling the Products data?

[#6]

Try using the Autonumber with Rand() function that should reduce the number of composite fields that you need to concatenate for a distinct value. Autnonumber(Rand(),Field&','&Field2)