topic Re: Update edited/new QVD records from DB in QlikView

Update edited/new QVD records from DB

Mon, 18 Mar 2013 10:49:49 GMT

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;

Re: Update edited/new QVD records from DB

sushil353 — Mon, 18 Mar 2013 11:02:09 GMT

Hi Try to give dates in quotes:

WHERE RL_EDIT_DATE = '$(vMaxEditDate)';

make change in other conditions too...

HTH

Sushil

Re: Update edited/new QVD records from DB

jonathandienst — Mon, 18 Mar 2013 11:23:40 GMT

I can see a couple of changes to get this to work.

First, change the Peek expressions so that the variables contain correctly formatted date and time:

...

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

...

let vMaxEditTime = Time(Peek('MaxEditTime',0,'RL_MaxTime'), 'hh:mm:ss');

...

(Use the correct date and time formats for your database. You can test this by manually submitting the SQL expression using a SQL front end like SQLMS or Toad)

Then your Where condition looks suspect. I suggest that you use:

SELECT * FROM PWIN171.dbo.RL

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

Hope that helps

Jonathan

Re: Update edited/new QVD records from DB

Mon, 18 Mar 2013 11:33:54 GMT

Re: Update edited/new QVD records from DB

Mon, 18 Mar 2013 11:42:18 GMT

Hi Sushil

Must I also place the RL_EDIT_TIME = '$(vMaxEditTime)' inside quotes? The RL_EDIT_TIME field is type INT and not a TIME field.

Re: Update edited/new QVD records from DB

Mon, 18 Mar 2013 11:42:18 GMT

Thanks tried your changes and got this…note the RL_EDIT_TIME field is actually INT type, e.g.: 54501 would be 05:45:01 AM

Re: Update edited/new QVD records from DB

Mon, 18 Mar 2013 11:47:20 GMT

Hi Guys (Thanks for the prompt responces)

I have used this as the RL_EDIT_TIME is int

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

This was the result. Any ideas whats wrong with the syntax near 'CONCATENATE'?

Re: Update edited/new QVD records from DB

Mon, 18 Mar 2013 11:47:40 GMT

Hi Guys (Thanks for the prompt responces)

I have used this as the RL_EDIT_TIME is int

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

This was the result. Any ideas whats wrong with the syntax near 'CONCATENATE'?

Re: Update edited/new QVD records from DB

sushil353 — Mon, 18 Mar 2013 11:48:18 GMT

Please upload a sample data and application..

Re: Update edited/new QVD records from DB

jonathandienst — Mon, 18 Mar 2013 11:51:20 GMT

I am afraid I cannot see anything in the box/image.

Re: Update edited/new QVD records from DB

sushil353 — Mon, 18 Mar 2013 11:54:48 GMT

Attached Pic is not visible??

Re: Update edited/new QVD records from DB

Mon, 18 Mar 2013 12:02:14 GMT

Hi Gents.

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

Cheers

Re: Update edited/new QVD records from DB

Tue, 19 Mar 2013 06:13:14 GMT

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;

Re: Update edited/new QVD records from DB

Tue, 19 Mar 2013 06:13:24 GMT

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;

Re: Update edited/new QVD records from DB

Tue, 19 Mar 2013 06:13:48 GMT

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;

Re: Update edited/new QVD records from DB

jonathandienst — Wed, 20 Mar 2013 14:03:26 GMT

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

Re: Update edited/new QVD records from DB

Wed, 03 Apr 2013 12:23:32 GMT

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;

Re: Update edited/new QVD records from DB

jonathandienst — Thu, 04 Apr 2013 11:21:36 GMT

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