Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
//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
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;
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
Hi Try to give dates in quotes:
WHERE RL_EDIT_DATE = '$(vMaxEditDate)';
make change in other conditions too...
HTH
Sushil
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
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.
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
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'?
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'?
Please upload a sample data and application..
I am afraid I cannot see anything in the box/image.