Is there a better way to right this script?

[#1]

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;

[miguelbraga]

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

[sunny_talwar]

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 ......

[vishsaggi · Accepted solution]

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;

[#5]

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.