Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
we are facing issue with Exists function. below is the script:
EDBExport:
LOAD [@1:3] as Num_Office,
[@4:4],
[@5:10] as Num_acct,
//'@1:3'&'@5:10' as [Office-Account],
[@11:160] as Email_Address,
[@161:n] as IDN_UUID,
'Qlikview Export' as Export
FROM
[filename1]
(fix, codepage is 1252);
EDBExport_N :
LOAD Num_Office,
Num_acct,
Num_Office&Num_acct as [Office-Account],
Email_Address,
IDN_UUID , Export
Resident EDBExport;
STORE EDBExport_N INTO EDBExport.qvd(qvd);
ProxyEdge:
LOAD [Report Date],
[Client Number],
Right([Account Number],9) as [Office-Account1],
[Inst Number],
[Inst Name],
Active,
Custodian,
[Account Name],
[VIF Sup]
FROM
[filename2] (ooxml, embedded labels, table is xxx);
MainTbl:
LOAD Num_Office as Office#,
Num_acct,
[Office-Account],
Email_Address,
IDN_UUID , Export
// , div(rowno(), 1000000) as Group
FROM EDBExport.qvd(qvd)
where Not Exists([Office-Account1],[Office-Account]) ;
MainTbl should have data for all office-accounts loaded from EDBExport_N, which are not in ProxyEdge.
This works fine.
BUT, when we add/remove any column from MainTbl script (like Group, now commented), the record count fluctuates.
This is a very raw script though, Can anyone help with the basic problem here?
Regards,
Manish
Can you check is there any duplicate rows exists for below?
Num_Office as Office#,
Num_acct,
[Office-Account],
Email_Address,
IDN_UUID , Export
It seems that are no relation between these fields and the exists-statement - are you sure that the record-count changed. Nevertheless here a very good post about the exists-function: The exists issue.
- Marcus
check for synthetic keys ?
EDBExport:
LOAD [@1:3] as Num_Office,
[@4:4],
[@5:10] as Num_acct,
//'@1:3'&'@5:10' as [Office-Account],
[@11:160] as Email_Address,
[@161:n] as IDN_UUID,
'Qlikview Export' as Export
FROM
[filename1]
(fix, codepage is 1252);
EDBExport_N :
LOAD Num_Office,
Num_acct,
Num_Office&Num_acct as [Office-Account],
Email_Address,
IDN_UUID , Export
Resident EDBExport;
STORE EDBExport_N INTO EDBExport.qvd(qvd);