When loading the same script from Qlikview to Qliksense; I am getting recode count mismatch and QVD file size is different; concatenate with not exist

[shanmathytitu]

When loading the same script from Qlikview to Qliksense; I am getting recode count mismatch and QVD file size is different; concatenate with not exists: I am using the same source, same query in both QV and QS. no change in script; only the environment is different. I

1.I am getting difference in file size between QV and QS

2.I am getting different count in QV and QS

It is a migration from Qlikview to Qliksense; There is no change in code only the below code is copied to QS. I am getting the above 2 issues. pls help.

Script:

WC_policy:

SQL select * from WC_policy;

store WC_policy into WC_policy_Q2;

DataHub:

load

policynumber,... from WC_policy_Q2.QVD;

concatenate (DataHub)

load

policynumber,... from WC_policy_history.qvd

where not exists(policynumber);

Record count:

Qliksense:
WC_policy << QueryResult
Lines fetched: 98,632
DataHub << WC_policy_Q2 Lines fetched: 98,632 DataHub << WC_policy_history Lines fetched: 98,912
Creating search index
Search index creation completed successfully

Qlikview:

Connected
WC_policy << ( 98,632 Lines fetched
DataHub << WC_policy_Q2 98,632 Lines fetched
DataHub << WC_policy_history 98,632 Lines fetched

[Vegar]

My best guess is that your not exists(policynumber) might be the issue.

There have been some changes in how QlikView/Qlik Sense interprets fields in some cases. So my guess is that you have a different hit rate in policynumber. 

Try loading policynumber as num(policynumber) or as text(policynumber) in your script and adjust your exist() accordingly: not exists(policynumber, text(policynumber)) 

Or like this:

exists(policynumber, num(policynumber)) 

[shanmathytitu]

Thanks for addressing this issue in a prompt manner. now the count in Qlikview and qliksense is same.

I used  below mentioned script . This one worked(where not exists(policynumber, text(policynumber));).

my question is in Qlikview " where not exists(policynumber)" worked. but in Qliksense, "where not exists(policynumber, text(policynumber))" only worked. Can you explain in detail where am i missing. What is the actual issue. is it because of qlikview and qliksense in different engine. kindly assist.

WC_policy:

SQL select * from WC_policy;

store WC_policy into WC_policy_Q2;

DataHub:

load

policynumber,... from WC_policy_Q2.QVD;

concatenate (DataHub)

load

policynumber,... from WC_policy_history.qvd

where not exists(policynumber,text(policynumber);

Record count:

Qliksense:
WC_policy << QueryResult
Lines fetched: 98,632
DataHub << WC_policy_Q2 Lines fetched: 98,633 DataHub << WC_policy_history Lines fetched: 98,633
Creating search index
Search index creation completed successfully

Qlikview:

WC_policy << ( 98,633 Lines fetched
DataHub << WC_policy_Q2 98,633 Lines fetched
DataHub << WC_policy_history 98,633 Lines fetched

[shanmathytitu]

Thanks for addressing this issue in a prompt manner. now the count in Qlikview and qliksense is same.

I used  below mentioned script . This one worked(where not exists(policynumber, text(policynumber));).

my question is in Qlikview " where not exists(policynumber)" worked. but in Qliksense, "where not exists(policynumber, text(policynumber))" only worked. Can you explain in detail where am i missing. What is the actual issue. is it because of qlikview and qliksense in different engine. kindly assist.

WC_policy:

SQL select * from WC_policy;

store WC_policy into WC_policy_Q2;

DataHub:

load

policynumber,... from WC_policy_Q2.QVD;

concatenate (DataHub)

load

policynumber,... from WC_policy_history.qvd

where not exists(policynumber,text(policynumber);

Record count:

Qliksense:
WC_policy << QueryResult
Lines fetched: 98,632
DataHub << WC_policy_Q2 Lines fetched: 98,633 DataHub << WC_policy_history Lines fetched: 98,633
Creating search index
Search index creation completed successfully

Qlikview:

WC_policy << ( 98,633 Lines fetched
DataHub << WC_policy_Q2 98,633 Lines fetched
DataHub << WC_policy_history 98,633 Lines fetched

Different tries beween QV and QS	Qlikview	Qliksense	Comment
where not exists (policynumber,num(policynumber))	Record count =98683	Record count =98963	But we have more data in Qliksense compared to QV due to high record count in QS
where not exists (policynumber)	Record count =98683	Record count =98963	But we have more data in Qliksense compared to QV due to high record count
where not exists (policynumber,text(policynumber))	Record count =99,052	Record count =98683	But we have more data in Qlikview compared to QS due to high record count in QS

Conclusion: where not exists (policynumber) in QV and where not exists (policynumber,text(policynumber)) in Qliksense worked with same record count w.r.t policynumber ;but for few fields like agencystate, producerusername data is not exactly matching. we have null records in Qliksense and we have data in Qlikview; kindly assist

[shanmathytitu]

Thanks for addressing this issue in a prompt manner. now the count in Qlikview and qliksense is same.

I used  below mentioned script . This one worked(where not exists(policynumber, text(policynumber));).

my question is in Qlikview " where not exists(policynumber)" worked. but in Qliksense, "where not exists(policynumber, text(policynumber))" only worked. Can you explain in detail where am i missing. What is the actual issue. is it because of qlikview and qliksense in different engine. kindly assist.

WC_policy:

SQL select * from WC_policy;

store WC_policy into WC_policy_Q2;

DataHub:

load

policynumber,... from WC_policy_Q2.QVD;

concatenate (DataHub)

load

policynumber,... from WC_policy_history.qvd

where not exists(policynumber,text(policynumber);

Record count:

Qliksense:
WC_policy << QueryResult
Lines fetched: 98,632
DataHub << WC_policy_Q2 Lines fetched: 98,633 DataHub << WC_policy_history Lines fetched: 98,633
Creating search index
Search index creation completed successfully

Qlikview:

WC_policy << ( 98,633 Lines fetched
DataHub << WC_policy_Q2 98,633 Lines fetched
DataHub << WC_policy_history 98,633 Lines fetched

Different tries between QV and QS	Qlikview	Qliksense	Comment
where not exists (policynumber,num(policynumber))	Record count =98683	Record count =98963	But we have more data in Qliksense compared to QV due to high record count in QS
where not exists (policynumber)	Record count =98683	Record count =98963	But we have more data in Qliksense compared to QV due to high record count
where not exists (policynumber,text(policynumber))	Record count =99,052	Record count =98683	But we have more data in Qlikview compared to QS due to high record count in QS

Conclusion: where not exists (policynumber) in QV and where not exists (policynumber,text(policynumber)) in Qliksense worked with same record count w.r.t policynumber. kindly assist why there is a difference between QV and QS for the mentioned tries