Update edited/new QVD records from DB

[#1]

Hi All

I have been trying to update my QVD with new or edited records on the DB unsuccessfully.  I have been using this script.  It siimply does not fetch and update the QVD with new or edited records.  Please help:

//Fetch my exitsing data in my QVD

RL_DATA:

LOAD * FROM (QVD);

//Determine the Max (RL_EDIT_DATE) and store as vMaxEditDate

RL_MaxDate:

LOAD MAX(RL_EDIT_DATE)as MaxEditDate

RESIDENT RL_DATA;

let vMaxEditDate = Peek('MaxEditDate',0,'RL_MaxDate');

DROP TABLE RL_DATA;

//Determine the Max(RL_EDIT_TIME) from the records which have theMax(RL_ADD_DATE) and store as vMaxEditTime

RL_DATA:

LOAD Max(RL_EDIT_TIME) as RL_EDIT_TIME

FROM (QVD)

WHERE RL_EDIT_DATE = $(vMaxEditDate);

RL_MaxTime:

LOAD Max(RL_EDIT_TIME) as MaxEditTime

RESIDENT RL_DATA;

let vMaxEditTime = Peek('MaxEditTime',0,'RL_MaxTime');

DROP TABLE RL_DATA;

//Not that I have the Max(RL_EDIT_DATE) and Max(RL_EDIT_TIME, I can use them to query the DB to return newer edited records.

ODBC CONNECT32 TO PRISMHC (XUserId is XVRGBZVMBDZeGXRNZTbA, XPassword is EAHfPZVMBDZeGXRNZLfB);

RL_New:

SQL

SELECT * FROM PWIN171.dbo.RL

WHERE RL_EDIT_DATE > $(vMaxEditDate)

and RL_EDIT_TIME > $(vMaxEditTime);

CONCATENATE LOAD * FROM C:\Users\tedder.bruce\Desktop\Qlikview Hirt & Carter\QVD\RL.QVD (QVD)

WHERE NOT EXISTS (ID);

STORE RL_New INTO C:\Users\tedder.bruce\Desktop\Qlikview Hirt & Carter\QVD\RL.QVD;

DROP TABLES RL_MaxDate,RL_MaxTime,RL_New;

[sushil353]

Attached Pic is not visible??

[#3]

Hi Gents.

Its works, I had put wrong refferences to RL_ instead of the QM_.....thanks you so much!

Cheers

[#4]

Hi Jonathan, Thanks for your help yesterday.  Re-ran the QVD refresh and it seemed to work.  The QVD seems to be missing 890 records versus when I use SQL to query the table in the DB.  I think the NOT EXISTS (ID) might be the issue? Should I simply not CONTCATENATE and Qlikview will update edited recorded and/or load new records? Please advise Johnathan.

Below is the scrip which APPEARS to run fine:

RL_DATA:

LOAD * FROM (QVD);

STORE RL_DATA INTO C:\Users\tedder.bruce\Desktop\Qlikview Hirt & Carter\QVD\RL_BACKUP.QVD;

RL_MaxDate:

LOAD MAX(RL_EDIT_DATE)as MaxEditDate

RESIDENT RL_DATA;

let vMaxEditDate = Date(Peek('MaxEditDate',0,'RL_MaxDate'), 'YYYY-MM-DD');

DROP TABLE RL_DATA;

RL_DATA:

LOAD Max(RL_EDIT_TIME) as RL_EDIT_TIME

FROM (QVD)

WHERE RL_EDIT_DATE = '$(vMaxEditDate)';

RL_MaxTime:

LOAD Max(RL_EDIT_TIME) as MaxEditTime

RESIDENT RL_DATA;

let vMaxEditTime = Num(Peek('MaxEditTime',0,'RL_MaxTime'));

DROP TABLE RL_DATA;

ODBC CONNECT32 TO PRISMHC (XUserId is XVRGBZVMBDZeGXRNZTbA, XPassword is EAHfPZVMBDZeGXRNZLfB);

RL_New:

SQL

SELECT * FROM PWIN171.dbo.RL (NOLOCK)

WHERE RL_EDIT_DATE > '$(vMaxEditDate)' Or (RL_EDIT_DATE = '$(vMaxEditDate)' And RL_EDIT_TIME > '$(vMaxEditTime)');

CONCATENATE LOAD * FROM C:\Users\tedder.bruce\Desktop\Qlikview Hirt & Carter\QVD\RL.QVD (QVD)

WHERE NOT EXISTS (ID);

STORE RL_New INTO C:\Users\tedder.bruce\Desktop\Qlikview Hirt & Carter\QVD\RL.QVD;

DROP TABLES RL_MaxDate,RL_MaxTime,RL_New;

[#5]

Hi Jonathan, Thanks for your help yesterday.  Re-ran the QVD refresh and it seemed to work.  The QVD seems to be missing 890 records versus when I use SQL to query the table in the DB.  I think the NOT EXISTS (ID) might be the issue? Should I simply not CONTCATENATE and Qlikview will update edited recorded and/or load new records? Please advise Johnathan.

Below is the scrip which APPEARS to run fine:

RL_DATA:

LOAD * FROM (QVD);

STORE RL_DATA INTO C:\Users\tedder.bruce\Desktop\Qlikview Hirt & Carter\QVD\RL_BACKUP.QVD;

RL_MaxDate:

LOAD MAX(RL_EDIT_DATE)as MaxEditDate

RESIDENT RL_DATA;

let vMaxEditDate = Date(Peek('MaxEditDate',0,'RL_MaxDate'), 'YYYY-MM-DD');

DROP TABLE RL_DATA;

RL_DATA:

LOAD Max(RL_EDIT_TIME) as RL_EDIT_TIME

FROM (QVD)

WHERE RL_EDIT_DATE = '$(vMaxEditDate)';

RL_MaxTime:

LOAD Max(RL_EDIT_TIME) as MaxEditTime

RESIDENT RL_DATA;

let vMaxEditTime = Num(Peek('MaxEditTime',0,'RL_MaxTime'));

DROP TABLE RL_DATA;

ODBC CONNECT32 TO PRISMHC (XUserId is XVRGBZVMBDZeGXRNZTbA, XPassword is EAHfPZVMBDZeGXRNZLfB);

RL_New:

SQL

SELECT * FROM PWIN171.dbo.RL (NOLOCK)

WHERE RL_EDIT_DATE > '$(vMaxEditDate)' Or (RL_EDIT_DATE = '$(vMaxEditDate)' And RL_EDIT_TIME > '$(vMaxEditTime)');

CONCATENATE LOAD * FROM C:\Users\tedder.bruce\Desktop\Qlikview Hirt & Carter\QVD\RL.QVD (QVD)

WHERE NOT EXISTS (ID);

STORE RL_New INTO C:\Users\tedder.bruce\Desktop\Qlikview Hirt & Carter\QVD\RL.QVD;

DROP TABLES RL_MaxDate,RL_MaxTime,RL_New;

[#6]

Hi Jonathan, Thanks for your help yesterday.  Re-ran the QVD refresh and it seemed to work.  The QVD seems to be missing 890 records versus when I use SQL to query the table in the DB.  I think the NOT EXISTS (ID) might be the issue? Should I simply not CONTCATENATE and Qlikview will update edited recorded and/or load new records? Please advise Johnathan.

Below is the scrip which APPEARS to run fine:

RL_DATA:

LOAD * FROM (QVD);

STORE RL_DATA INTO C:\Users\tedder.bruce\Desktop\Qlikview Hirt & Carter\QVD\RL_BACKUP.QVD;

RL_MaxDate:

LOAD MAX(RL_EDIT_DATE)as MaxEditDate

RESIDENT RL_DATA;

let vMaxEditDate = Date(Peek('MaxEditDate',0,'RL_MaxDate'), 'YYYY-MM-DD');

DROP TABLE RL_DATA;

RL_DATA:

LOAD Max(RL_EDIT_TIME) as RL_EDIT_TIME

FROM (QVD)

WHERE RL_EDIT_DATE = '$(vMaxEditDate)';

RL_MaxTime:

LOAD Max(RL_EDIT_TIME) as MaxEditTime

RESIDENT RL_DATA;

let vMaxEditTime = Num(Peek('MaxEditTime',0,'RL_MaxTime'));

DROP TABLE RL_DATA;

ODBC CONNECT32 TO PRISMHC (XUserId is XVRGBZVMBDZeGXRNZTbA, XPassword is EAHfPZVMBDZeGXRNZLfB);

RL_New:

SQL

SELECT * FROM PWIN171.dbo.RL (NOLOCK)

WHERE RL_EDIT_DATE > '$(vMaxEditDate)' Or (RL_EDIT_DATE = '$(vMaxEditDate)' And RL_EDIT_TIME > '$(vMaxEditTime)');

CONCATENATE LOAD * FROM C:\Users\tedder.bruce\Desktop\Qlikview Hirt & Carter\QVD\RL.QVD (QVD)

WHERE NOT EXISTS (ID);

STORE RL_New INTO C:\Users\tedder.bruce\Desktop\Qlikview Hirt & Carter\QVD\RL.QVD;

DROP TABLES RL_MaxDate,RL_MaxTime,RL_New;

[jonathandienst]

Bruce

The Not Exists(ID) will block loading duplicate values of ID, so if those values have IDs that are duplicates, then the not exists is the problem. If you need to bring these in, then you will need to create a temporary ID field to control the load.

In your SELECT....

     SQL SELECT *,

          ID As T_ID

      FROM   ....

And in your QVD load,

     LOAD ... WHERE Not Exists(T_ID, ID);

(This will break the optimised load for the QVD)

Regards

Jonathan

[#8]

Hi Jonathan

Sorry for being such a dubm-ass.

I seem to be duplicating records now. I need to REPLACE the records in the QVD with records from the SQL SELECT where the ID matches AND add any that dont EXIST in the QVD.  Any ideas please?

My Query is below:

QM_DATA:

LOAD * FROM (QVD);

STORE QM_DATA INTO C:\Users\tedder.bruce\Desktop\Qlikview Hirt & Carter\QVD\QM_BACKUP.QVD;

QM_MaxDate:

LOAD MAX(QM_EDIT_DATE)as MaxEditDate

RESIDENT QM_DATA;

let vMaxEditDate = Date(Peek('MaxEditDate',0,'QM_MaxDate'), 'YYYY-MM-DD');

DROP TABLE QM_DATA;

QM_DATA:

LOAD Max(QM_EDIT_TIME) as QM_EDIT_TIME

FROM (QVD)

WHERE QM_EDIT_DATE = '$(vMaxEditDate)';

QM_MaxTime:

LOAD Max(QM_EDIT_TIME) as MaxEditTime

RESIDENT QM_DATA;

let vMaxEditTime = Num(Peek('MaxEditTime',0,'QM_MaxTime'));

DROP TABLE QM_DATA;

ODBC CONNECT32 TO PRISMHC (XUserId is XVRGBZVMBDZeGXRNZTbA, XPassword is EAHfPZVMBDZeGXRNZLfB);

QM_New:

SQL

SELECT * FROM PWIN171.dbo.QM (NOLOCK)

WHERE QM_EDIT_DATE > '$(vMaxEditDate)' Or (QM_EDIT_DATE = '$(vMaxEditDate)' And QM_EDIT_TIME > '$(vMaxEditTime)');

CONCATENATE LOAD * FROM C:\Users\tedder.bruce\Desktop\Qlikview Hirt & Carter\QVD\QM.QVD (QVD);

//WHERE NOT EXISTS (ID);

STORE QM_New INTO C:\Users\tedder.bruce\Desktop\Qlikview Hirt & Carter\QVD\QM.QVD;

DROP TABLES QM_MaxDate,QM_MaxTime,QM_New;

[jonathandienst]

Bruce

To do an incremental load to replace updated records, you need some way to identify the changed records, such as a last updated date/time field. You also need a unique key for each record (no duplicates). If your data does not meet these requirements, then you will not be able to update the QVD incrementally - you will have to do a full reload instead.

If your data meets the above requirements, then you can incremental load with these steps:

• Determine the last load time. This could be the maximum value of the last updated field in your QVD, or some other stored value indicating when the QVD was last refreshed, such as the QvdCreateTime function.
• Load from the source all the records that have a last updated date greater than the last load time.
• Load the date from the QVD, excluding those that were loaded from the source (because these records have been updated). Use Not(Exists()) as discussed earlier - but this does require that the ID field (or whatever key you choose) is unique for each record.
• Store the updated QVD.

I hope that helps. If you have more questions, I suggest that you open a new thread (referencing this thread if you like). This one has been marked as answered, so you are less likely to get more help here.

Regards

Jonathan