Need help on grouping fields from a dimension

[eupsilon]

Hi! 

I am obviously new to Qlik and haven't been able to find help for the following:

I have a table with various dimensions. one of them is called Product.

In Product, I have multiple values: P1A, P1B, P1C, P2A, P2B, P3A, P3B, P3C.

Instead of having visualization displaying all of these values (P1A to P3C), I would like to group them in Product 1 (includes P1A, P1B, P1C), Product2 (includes P2A, P2B,), and Product 3 (includes P3A, P3B, P3C). This way, my graphs, filters, etc can just have 3 values.

How can I do this in the easiest way possible?

Also, what happens if next time I load may table, I get new values P4A, and P4B, for example.

Thank you so much for your help!

eupsilon

[sunny_talwar]

Is P1A, P1B... etc are how the product are named or was this just an example? If this is how they are named and first two will be how the products will be grouped... then you can may be try this

LOAD Left(Product, 2) as ProductGroup,
  Product,
  ....
FROM ...;

Now you can use ProductGroup as your dimension

[eupsilon]

Thank you for the quick reply, Sunny!
No, I cannot use the product name prefixes to populate the groups, but there are only about 10 different types of products, so I pretty much know which values to look for. Do I have aciioke of questions:

1. I am importing csv data. A which point in workflow do I need to make use of the script?
2. How do I modify it to group known the known values gut the products?
3. To be future proof, if a value is new and therefore unknown, can we have the script put these in a "other" category?

Thank you so much!

[sunny_talwar]

---

@eupsilon wrote:
1. I am importing csv data. A which point in workflow do I need to make use of the script?

You can do this when after the data load for your csv data. But you can load the mapping data before or after you load your CSV data. (pseudo code to follow)

---

@eupsilon wrote:
2. How do I modify it to group known the known values gut the products?

---

Use ApplyMap

---

@eupsilon wrote:
3. To be future proof, if a value is new and therefore unknown, can we have the script put these in a "other" category?

---

This can be handled with ApplyMap also.

Pseudo Code

MappingTable:
Mapping
LOAD Product,
    ProductGroup
FROM ....;

Fact:
/*Load your data from the CSV File*/
LOAD ....,
    Product,
    ApplyMap('MappingTable', Product, 'Other') as ProductGroup
FROM ....;