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]

Hi Try to give dates in quotes:

WHERE RL_EDIT_DATE = '$(vMaxEditDate)';

make change in other conditions too...

HTH

Sushil

[jonathandienst · Accepted solution]

Hi

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

[#4]

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.

[#5]

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

[#6]

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'?

[#7]

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'?

[sushil353]

Please upload a sample data and application..

[jonathandienst]

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