Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

comparing two tables with same structure

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

1 Solution

Accepted Solutions
Highlighted

Re: comparing two tables with same structure

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

View solution in original post

2 Replies
Highlighted

Re: comparing two tables with same structure

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

View solution in original post

Highlighted
Not applicable

Re: comparing two tables with same structure

Thank you!

Olivia