topic Re: mapping multiple values into 1 category in QlikView

mapping multiple values into 1 category

Wed, 05 Sep 2012 16:03:03 GMT

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!

Re: mapping multiple values into 1 category

Thu, 06 Sep 2012 13:31:20 GMT

Bump. Please provide at least high level idea.

Re: mapping multiple values into 1 category

sybase67 — Thu, 06 Sep 2012 17:16:20 GMT

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

Re: mapping multiple values into 1 category

sybase67 — Thu, 06 Sep 2012 17:17:08 GMT

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

Re: mapping multiple values into 1 category

Thu, 06 Sep 2012 20:17:27 GMT

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?

Re: mapping multiple values into 1 category

Thu, 06 Sep 2012 20:20:49 GMT

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)