Script - Mapping to multiple values in Single Column

[yastest]

Hi I'm looking at Qlik Sense Cloud.

I have a table with multiple values in a column, they are not named correctly so would like to use mapping table for the correct values.

The challenge is that I want to then be able to create summary graph of count of products.

I have thousands of rows of data so don't want to end up with an unpivoted table with additional rows of data.

Any thoughts on this would be great.

The product column values are separated by semicolon, but they are not the correct value.

This is the mapping table.

[MarcoWedel]

https://help.qlik.com/en-US/sense/May2023/Subsystems/Hub/Content/Sense_Hub/Scripting/MappingFunction...

[yastest]

This gives me first part of solution.

The second part is how do I count the products by month/year to see trends? Any thoughts?

[MarcoWedel]

what would be your expected result given some sample data?

[yastest]

A graph or table that shows count by month/year

For example:

Jan 21               

01 Toy     =   2

02 Home  = 1

03 Car  = 2

[Vegar]

I see two possible solutions for his.

1. One measure per product

This is only a doable solution when you have very few products. In your sample it would be three expressions. 

• Count({<Product*={'01 Toys'}>} [Sales ID]
• Count({<Product*={'02 Home'}>} [Sales ID]
• Count({<Product*={'03 Car'}>} [Sales ID]

2. Creating single Product dimension

In order to use Product as a dimension in a chart you need each value separated as a single value somehow. You write that you don't want to split your table into one row per Product, so I suggest that you split the Product in a new Product dimension table.

/*Load you sales data, the data from your example.*/

SALES: LOAD * From SalesData;

Rename Field Product to %product_key;

/*Create a dimension table wher your original value is the primary key and every semicolon separated value becomes a dimension value.

(You might want to do your applymap in this table and not your SALES data table, especially if you have a lot of data transactions)*/

PRODUCT_DIM: Load distinct %product_key, subfield (%product_key, ';') as Product Resident SALES;

[yastest]

Thanks for suggestions, it,s helped provide me solution - nearly there!!! 

1. Original Table excluding Product data (Table 1)

2. Original Table (Table 2) just with Sales ID and Product. Product is split by delimiter of ; so it product appears on each row. Ontop of that I've done a TRIM to remove leading trailing spaces. 

I've then mapped and returned lookup values for products.

3. Created products key which has 11 items.

4. The tables are linked and give me numbers by products, etc.

Looking at now deleted duplicate or empty data of products to reduce rows of data.

Not sure if best method, but seems to give me data needed to trends and counts.

[elakkians]

Hi @yastest ,

If you have a Proper delimiter in the 'Product' column, You can use Subfield(Product,';') to split the columns and do the mapping.

[yastest]

---

@elakkians wrote:

Hi @yastest ,

If you have a Proper delimiter in the 'Product' column, You can use Subfield(Product,';') to split the columns and do the mapping.

---

Great suggestion.