comparing two tables with same structure

[#1]

Hello,

I have 2 tables with invoices and receipts documents from 2 months - May and June, having same fields structure (doc no, doc date, customer name, customer ID, value etc.), and I want to compare them as below

1. "Closed docs" - only documents from May file which aren't in June file;

2. "New docs" - only documents from June file which aren't in May file;

3. "Open docs" - only documents which exists in May and June files.

Haw can I transpose those conditions into script?

Thank you!

Olivia

[hic · Accepted solution]

I would suggest using the Exists() function in something similar to the following:

DocNoMayIndex:

Load distinct [doc no] as DocNoMay from MayData;

DocNoJunIndex:

Load distinct [doc no] as DocNoJun from JunData;

AllDocuments:

Load *,

          if(Exists(DocNoMay,[doc no]) and not Exists(DocNoJun,[doc no]),'Closed',

          if(not Exists(DocNoMay,[doc no]) and Exists(DocNoJun,[doc no]),'New',

          'Open')) as DocType

          From ...

HIC

[#3]

Thank you!

Olivia