Find the records with common key values

[YHC]

Hi there!

I have two tables with the same column and row names. Except for the keys, the values in the tables are different, so I cannot use INNER JOIN or INNER KEEP. How can I find the records with common key values in both tables and export them separately?

5

thanks!

[Mark_Little]

Hi,

I am not a 100% i understand your use case, but i do

Load the Keys from Table 1,

Left Join Keys from Table 2,

Then Resident load where all fields have a value.

Then use this to load The data from the table with the where exists function.

[BrunPierre]

@Mark_Little If I have correctly understood, then along these lines. 

MatchedKeys:
LOAD No.
FROM
[...\sum_trial.xlsx]
(ooxml, embedded labels, table is Sheet1);

Concatenate

T2:
LOAD No., 
If(Exists(No.),1) as MatchedFlag
FROM
[...\sum_trial.xlsx]
(ooxml, embedded labels, table is Sheet2);

S1:
LOAD No.,
     Sales, 
     Name, 
     Name2,
     'Sheet 1' as Source
FROM
[...\sum_trial.xlsx]
(ooxml, embedded labels, table is Sheet1);

S2:
LOAD No., 
     Sales, 
     Name, 
     Name2,
     'Sheet 2' as Source
FROM
[...\sum_trial.xlsx]
(ooxml, embedded labels, table is Sheet2);

Left Join
LOAD * Resident MatchedKeys;

NoConcatenate
Final:
LOAD * Resident S1
Where MatchedFlag = 1;

DROP Table MatchedKeys, S1;