Combine two fields from two tables based on the values

[GabrielOtet]

Dear all,

I have the following problem to solve and I cannot get it done until now:

I have two tables (Table A with fewer samples and Table B with more samples and both have different fields).

Table A

Field A.1

Field A.2

Key 1 

Table B

Field B.1

Field B.2

FieldB.3

.......

Key2

The Key fields from both tables are linked. It means that one value from Key 1 might contain multiple values that at one point can be found in Key 2 fields, as below:

Key 1 = 1234, 2345, 5434, 6355

Key 2= 1234

Key 2 = 2345

Key 2 = 5434

..........

Basically, Key 1 will contain randomly in the same field a value that is at one point contained by Key 2 from the second table. The length of Key 1 is random and can contain just one value or multiple.

What I want to do is to create a Key 3 field that will kind of link the two tables. I'm thinking to add an additional field to Table B (the longer ones) and this field will contain Key 1 data as soon as the number in Key 2 is contained on it. In this case, the link will be made by Qlik.

The problem is that my ideas are useless so far and I could not do it.

Does anybody have an idea on how to solve it?

[lironbaram · Accepted solution]

Hi , the easiest way will be to create  a middle field like this 

TableC :
load distinct [Key 1],
              subfield([Key 1],',') as [Key 2]
resident TAbleA;

[GabrielOtet]

Hello Lironbaram,

many thanks for the answer. It seems to work with no error messages. I have to check further and see what is the impact for other calculations of having additional fields in place.

Anyhow, I'm a bit confused about how the solution really works. Can you please explain it a bit?