Creating Key Associations via Search/"Contains"

[#1]

I'm working on a dashboard combining various types of service management records. For most of these I have a six digit "ID" which qlikview's automatic associations have successfully linked. There is one field, in tableX, with a freeform text that can contain many of these six digit IDs. I'd like for the ID association to associate to any row that has that ID anywhere in said text field. Obviously this means I'd like rows from tableX to be associated to multiple ID rows elsewhere in the data structure.

Example:

tableY:

ROW | ID

A | 12345

B | 22345

C | 32345

tableX:

ROW | IDMATCH

A | text( 12345 )text( 22345 )text

B | text( 32345 )text( 42345 )text

C | text( 56789 )text( 67890 )text

Obviously if this needs to be solved at the data structure level prior to inputting the tables into Qlik then that is what I'll have to do. However, if there's a way to do it within Qlik's internal functions and logic I'd be interested in learning it, because otherwise the automatic associations are quite handy.

Thanks in advance and let me know anything I wasn't clear on!

[Anonymous · Accepted solution]

I think you have to create many-to-many relation between table X and table Y by using additional table:

TablyXY:

IDMATCH,                                   ID

text( 12345 )text( 22345 )text,     12345

text( 12345 )text( 22345 )text,     22345

text( 32345 )text( 42345 )text,     32345

text( 32345 )text( 42345 )text,     42345

text( 56789 )text( 67890 )text,     56789

text( 56789 )text( 67890 )text,     67890

It will be associated with TableY by ID, and with TableX by IDMATCH.

[#3]

Yeah, I figured I'd have to do something like that. Thanks.

[Anonymous]

Here is an example.  I bet it is a most ineffective because of the loops, just a quick exercise.  Hope there is a better way...