Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
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;
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
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 ......
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;
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.