Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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