How to refer the 1 record to another

[krzys]

Hey everyone,

I came across a case I bet will be rather easy for experienced Qlik users on this forum. I have 2 tables related to each other (data sample and the QS app attached).

Both tables are connected with a simple %KEY field like the underneath:

LOAD
"NEW O ID",
"New O P",
"New O Owner",
"New O Field E",
"%KEY_OLD ORG ID"
FROM [lib://DataFiles/Tab 1.xlsx]
(ooxml, embedded labels, table is Sheet2);

LOAD
"%KEY_OLD ORG ID",
"Old O P",
"Old O Owner",
"Old O Field E"
FROM [lib://DataFiles/Tab 2.xlsx]
(ooxml, embedded labels, table is Sheet1);

The result here is a combined table that looks like this:

This is the correct result when looking at the data model however I would like it to look like this:

where record 0066A000004YjdrQACis correlated to 0066A000003wIJJQA2 (per the tables) but also to 0066A000004YjdrQAC. And thanks to this artificial relationship I would like to be able to pull all the related info.

All tips are very welcome

Thanks

[krzys]

Hi again,

I load the same table again just for the ID I would like to have in the data set and used replace() for the %KEY the way I want.

The main question is how to do it smarter since I cannot reload the same table 2000 times for 2000 %KEYs to be replaced. Any ideas?

LOAD
"NEW O ID",
"New O P",
"New O Owner",
"New O Field E",
"%KEY_OLD ORG ID"
FROM [lib://DataFiles/Tab 1.xlsx]
(ooxml, embedded labels, table is Sheet2);

Concatenate

LOAD
"NEW O ID",
"New O P",
"New O Owner",
"New O Field E",
replace("%KEY_OLD ORG ID",'0066A000003wIJJQA2','0066A000004YjdrQAC') as "%KEY_OLD ORG ID"
FROM [lib://DataFiles/Tab 1.xlsx]
(ooxml, embedded labels, table is Sheet2);

LOAD
"%KEY_OLD ORG ID",
"Old O P",
"Old O Owner",
"Old O Field E"
FROM [lib://DataFiles/Tab 2.xlsx]
(ooxml, embedded labels, table is Sheet1);