Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
In my dashboard I am using the below code to incremental load Salesforce.com data. This code is taken from the demo app that is available on QlikView web site.
I am not able to understand how this code handles the Accounts that get deleted within SFDC? After running the below code for 30 days, I now have a around 100 accounts in my QVD which are not in SFDC (deleted ones). I can't shift to full load as its a huge data and refresh cycle is every 30mins.
Can any one had similar problems?
Account_SQL:
SQL SELECT
*
FROM Account
WHERE LastModifiedDate >= $(vLastExecTime) and LastModifiedDate < $(vExecTime);
// For the 1st reload, this section will be skipped.
if Not $(FirstReload) and not isnull(QvdCreateTime('$(vQVDPath)Account_SQL.QVD'))then
Load *
FROM $(vQVDPath)Account_SQL.qvd (qvd)
WHERE NOT EXISTS(Id);
end if
if NoOfRows('Account_SQL') > 0 then
STORE Account_SQL INTO $(vQVDPath)Account_SQL.qvd;
Account:
Load
*
RESIDENT Account_SQL;
DROP TABLE Account_SQL;
End if
Regards,
Murali
Even the resident load didn't give much improvement with respect to load times.
The work around I have used is (in agreement with business) to run the deleted records logic (inner join) only during the first reload of the day. Rest all reloads follow normal incremental load.
The only downside of this is, the deleted records of today will only reflect tomorrow in the dashboard. Business are kind of ok with it.
Thanks all for your valuable inputs.
Hi Muralidhar,
If you want to remove the deleted records from the QVD then you need to fetch the ids of the users and do the inner join to the qvd. Please check the script below from Help file
Let ThisExecTime = Now( );
//Load modified records from Database
QV_Table:
SQL SELECT PrimaryKey, X, Y FROM DB_TABLE
WHERE ModificationTime >= #$(LastExecTime)#
AND ModificationTime < #$(ThisExecTime)#;
//Concatenate already existing records in QVD to the records modified/inserted in the database
Concatenate LOAD PrimaryKey, X, Y FROM File.QVD
WHERE NOT EXISTS(PrimaryKey);
//Do inner join to remove the duplicates
Inner Join SQL SELECT PrimaryKey FROM DB_TABLE;
Hope this helps you.
Regards,
Jagan.
I have done this but the final Inner join is taking long time.
Are there any other alternate options available?
Hi Muralidhar,
I think there is no other option you have to do the inner join then only the deleted records are eliminated.
Are you getting only the primary value or retrieving all the values?
ie., Inner Join SQL SELECT PrimaryKey FROM DB_TABLE;
or using
Inner Join SQL SELECT * FROM DB_TABLE;
Since Select * takes long time to load.
Regards,
Jagan.
Yes. I did only give the primary key column in the inner join select.
The incremental load without inner join finished in 8 mins for 9 tables. where as
The incremental load with inner join is running from past 90 mins and still 3 tables are left to complete.
So I am sure I can't go with this Logic.
Hi Muralidhar,
Instead of joining directly fetch data and store in resident table and then inner join.
Temporary:
SQL SELECT PrimaryKey FROM DB_TABLE;
Inner Join LOAD * RESIDENT Temporary;
DROP TABLE Temporary;
Hope this helps you.
Regards,
Jagan.
Even the resident load didn't give much improvement with respect to load times.
The work around I have used is (in agreement with business) to run the deleted records logic (inner join) only during the first reload of the day. Rest all reloads follow normal incremental load.
The only downside of this is, the deleted records of today will only reflect tomorrow in the dashboard. Business are kind of ok with it.
Thanks all for your valuable inputs.
Hi Muralidhar,
I notice that you had successful connecting and extracting data from Salesforce.com table using the demo app from qlikview site.
I was using the same demo app, however connection tested successful but when run the scripts, it return me zero record. I try create select statement but no tables display on my screen.
I am using SalesForceDLL.dll version 11 and Qlikview version 11.
Is there additonal requirements to be setup in Salesforce.com?
Thanks and appreciates your expert advise.
Roslan.
my email is rlan1308@gmail.com
Roslan,
Please double check the user id that you are using to connect to Salesforce.com has sufficient previllages to read data.
Regards,
Murali