Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

mapping multiple values into 1 category

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!

5 Replies
Not applicable
Author

Bump. Please provide at least high level idea.

sybase67
Contributor II
Contributor II

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
Contributor II
Contributor II

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...

Not applicable
Author

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?

Not applicable
Author

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)