How to link different fields

[BtBread]

Hello experts.

How is it possible to link the following data?
I have a table with actions that affect several product groups. In one record of the action table, several product groups are specified in one field:

Action-ID	Description	AffectedGroups
1	xxxx	101,102,103
2	xxxx	103,210
3	xxxx	400
4	xxxx	101

In the product table I have exactly one product group for each product.

ArticleID	Description	ProductGroup
123456	xxxxx	101
123457	xxxxx	102
123458	xxxxx	103
234567	xxxxx	210
234568	xxxxx	400

My request: When I select one of the measures, I want to restrict the product table to the relevant product groups.
Who can help me?
Do I already have to solve this in the script?

Thanks in advance

[vunguyenq89 · Accepted solution]

Yes, to link the two tables on fields (AffectedGroups, ProductGroup), you will need to flatten the Action table (i.e having one row for each AffectedGroups value). This can be done with SubField() function. You also need to use the same name for the two linking fields for Qlik to automatically create an association.

Sample script is as follows:

Actions_Temp:
LOAD * INLINE [
Action-ID	Action-Description	AffectedGroups
1	xxxx	101,102,103
2	xxxx	103,210
3	xxxx	400
4	xxxx	101
] (delimiter is '\t');

Products:
LOAD * Inline [
ArticleID	Article-Description	ProductGroup
123456	xxxxx	101
123457	xxxxx	102
123458	xxxxx	103
234567	xxxxx	210
234568	xxxxx	400
] (delimiter is '\t');

Actions: 
LOAD 
	[Action-ID],
	[Action-Description],
	Subfield(AffectedGroups,',') as ProductGroup
Resident Actions_Temp;

DROP Table Actions_Temp;

Result is a flattened Actions table:

Hope this helps,

BR,

Vu Nguyen