Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

How can I join two tables but have a log of records which don't exist in one set?

Hello,

I am trying to import data from two sources, compare values where keys match, but also report on fields where there was no matching key.

So, let's say I have two fields TRADE_ID and VALUE, and I have data like the below

Table LEFT: [123, 1], [124, 2], [125, 3]

Table RIGHT: [123,2], [124,0]

I want to have an object that shows me that there are value differences for 123 and 124, and a seperate object that shows me 125 is missing from the RIGHT.

If I do a JOIN (INNER or OUTER) when loading the second table (Right), then I am able to reference Left after the join as a resident table.  However I am unable to reference Right - I get table not found.  that means I can achieve my objective to compare, but not report on missing records.

From reading the documentation it sounds like I need to use KEEP, but I do that my script fails to find the field VALUE, implying something has gone wrong.  I've pasted the latest code I'm struggling with below:

SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='£#,##0.00;-£#,##0.00';
SET TimeFormat='hh:mm:ss';
SET DateFormat='DD/MM/YYYY';
SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

ODBC CONNECT TO [nygrimsd;DBQ=nygrimsd] (XUserId is TdNEbTJOJLbSWZdLSLZGWXZNGLaOWPVOTLZEXYD, XPassword is McaORKNKfKYGWRdNVTbIXYMG);

left:
LOAD TRADE_ID,
     VALUE as productionValue
FROM
[\\dbg.ads.db.com\LON-USERS-U\VF01_USERS01\smitada\config\Desktop\export1.xls]
(biff, embedded labels, table is [Export Worksheet$]);

right:
INNER KEEP SQL SELECT TRADE_ID, VALUE
FROM VERTEX_REPORT.REPORT
WHERE RISK_GROUP LIKE 'DBNY_FIXED1%'
AND REPORT_NAME = 'PRICE_COB'
AND BUSINESS_DATE = '26-OCT-12'
AND TRADE_ID IN(
'N594682N'
,'N594932N'
,'N595080N'
,'N595130N'
,'N595255N'
,'N595377N'
,'N595385N'
,'N595393N'
,'N1263110N'
,'N1263562N');


derived:
LOAD distinct TRADE_ID, Round(productionValue - VALUE) as valueImpact
Resident left;
trace counting rows;
$c = (NoOfRows (left));
trace starting loop;
for n=0 to 10
if(exists(TRADE_ID, peek(left, n))) then
  trace TRADE_ID exists in left;
  else
  trace TRADE_ID does not exist in left;
endif
next

Here's the error I get

Field not found - <VALUE>

derived:

LOAD distinct TRADE_ID, Round(productionValue - VALUE) as valueImpact

Resident left

Thanks in advance for any help...

1 Reply
MVP
MVP

Re: How can I join two tables but have a log of records which don't exist in one set?

Maybe John's solution here is of interest for you:

http://community.qlik.com/message/138549#138549

Regards,

Stefan

Community Browser