topic Re: Is there a better way to right this script? in QlikView

Is there a better way to right this script?

Wed, 25 Nov 2020 16:16:04 GMT

Good Morning,

I have the following script that pulls data from a database via an ODBC connection.

However, this table holds 1.5 million records and when I try to filter the data in the script it becomes very tempremental and sometimes the data never finishes loading.

Am I writing this script in the most efficient way?

LOAD CallType, ContactType, ContactUnique, CreatingUser, CreationDate, DiaryDate, DiaryEntryType, LinkingKeyValue, SiteUnique, Subject, UniqueID

WHERE Date(CreationDate,'DD/MM/YYYY') >= Today()-90 AND Date(CreationDate,'DD/MM/YYYY') <= Today()-84 ;

SQL SELECT CallType, ContactType, ContactUnique, CreatingUser, CreationDate, DiaryDate, DiaryEntryType, LinkingKeyValue, SiteUnique, Subject, UniqueID FROM JOURNAL0008;

Re: Is there a better way to right this script?

miguelbraga — Thu, 22 Sep 2016 16:14:45 GMT

Hey there,

Try load everything into one table and then use Store Table to create a QVD which is faster to load than loading directly from the source database. Only when you load from QVD you can apply the WHERE clause.

Best regards,

D.A. MB

Re: Is there a better way to right this script?

sunny_talwar — Thu, 22 Sep 2016 16:17:06 GMT

May be restrict your data within the SQL itself. This way you will only bring the data you need from SQL into QlikView which would probably save you a lot of time. Exact syntax can be determined by a SQL guy. but I am thinking would be like Where Creation date between ......

Re: Is there a better way to right this script?

vishsaggi — Thu, 22 Sep 2016 16:17:07 GMT

DId you try putting the where in SQL clause like below or in the Preceding Load ?

LOAD CallType,

ContactType,

ContactUnique,

CreatingUser,

CreationDate, DiaryDate, DiaryEntryType,

LinkingKeyValue, SiteUnique, Subject, UniqueID;

SQL SELECT CallType, ContactType, ContactUnique,

CreatingUser, CreationDate, DiaryDate, DiaryEntryType,

LinkingKeyValue, SiteUnique, Subject, UniqueID

FROM JOURNAL0008

WHERE Date(CreationDate,'DD/MM/YYYY') >= Today()-90 AND Date(CreationDate,'DD/MM/YYYY') <= Today()-84;

------------------------------- QV Preceding Load.

LOAD *

WHERE Date(CreationDate,'DD/MM/YYYY') >= Today()-90 AND Date(CreationDate,'DD/MM/YYYY') <= Today()-84;

LOAD CallType,

ContactType,

ContactUnique,

CreatingUser,

CreationDate, DiaryDate, DiaryEntryType,

LinkingKeyValue, SiteUnique, Subject, UniqueID;

SQL SELECT CallType, ContactType, ContactUnique,

CreatingUser, CreationDate, DiaryDate, DiaryEntryType,

LinkingKeyValue, SiteUnique, Subject, UniqueID

FROM JOURNAL0008;

Re: Is there a better way to right this script?

Tue, 04 Oct 2016 14:51:54 GMT

Thank you all for your answers.

I tried a few different options, but in the end moving the where statement to the SQL clause resulted in the shortest amount of time to export the data.

I didn't try the create the QVD file, but I did utilise the STORE command to create CSV files, so thank you very much for this.

My ODBC connection still isn't 100% stable and does sometimes cause the data to constantly loop, but this happens much less frequently.