Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
manishnarang
Contributor

Issue with Exists behaviour

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

3 Replies
MVP
MVP

Re: Issue with Exists behaviour

Can you check is there any duplicate rows exists for below?   

Num_Office as Office#,

Num_acct,
[Office-Account],
Email_Address,
IDN_UUID , Export

Re: Issue with Exists behaviour

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

sasiparupudi1
Honored Contributor III

Re: Issue with Exists behaviour

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

Community Browser