topic Find the records with common key values in App Development

Find the records with common key values

YHC — Fri, 07 Jul 2023 05:58:33 GMT

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?

thanks!

Re: Find the records with common key values

Mark_Little — Fri, 07 Jul 2023 06:28:02 GMT

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.

Re: Find the records with common key values

BrunPierre — Fri, 07 Jul 2023 10:09:29 GMT

@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;