Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear community,
I'm having some scripting issues with trying to create an optimised qvd.
Below is my script pulling information from a MySQL database - the Where Clause isn't working - please can you help?
TempT:
Load
timestamp(max(inserted_date)) as MaxID
Resident PackBench;
Let MaxID = peek('MaxID');
Drop Table TempT;
ODBC CONNECT32 TO dbserver;
Concatenate (PackBench)
SnapPackBench:
Load
collectdate,
cslsupplierref,
easyWebref,
`export_stamp`,
`inserted_date`,
`lock_stamp`,
ownerid,
`processed_stamp`,
`qc_reason`,
`qc_stamp`,
`qc_user`,
`snap_ref`,
stu;
SQL SELECT collectdate,
cslsupplierref,
easyWebref,
`export_stamp`,
`inserted_date`,
`lock_stamp`,
ownerid,
`processed_stamp`,
`qc_reason`,
`qc_stamp`,
`qc_user`,
`snap_ref`,
stu
FROM snap.shipmentlog
Where `inserted_date`>$(MaxID);
Try this:
TempT:
Load Max(inserted_date) as MaxID
Resident PackBench;
Let MaxID = Date(Peek('MaxID'), 'YYYY-MM-DD hh:mm:ss');
Drop Table TempT;
ODBC CONNECT32 TO dbserver;
Concatenate (PackBench)
SnapPackBench:
Load
collectdate,
cslsupplierref,
easyWebref,
`export_stamp`,
`inserted_date`,
`lock_stamp`,
ownerid,
`processed_stamp`,
`qc_reason`,
`qc_stamp`,
`qc_user`,
`snap_ref`,
stu;
SQL SELECT collectdate,
cslsupplierref,
easyWebref,
`export_stamp`,
`inserted_date`,
`lock_stamp`,
ownerid,
`processed_stamp`,
`qc_reason`,
`qc_stamp`,
`qc_user`,
`snap_ref`,
stu
FROM snap.shipmentlog
Where `inserted_date`> '$(MaxID)';
Try
Where inserted_date>'$(MaxID)';
Have you tried running the SQL query outside QlikView? You need to make sure that output of $(MaxID) is in the same format as the one you use when you run it outside QlikView. I know Oracle takes in DD-MMM-YYYY format. So if the date isn't supplied in the same format, it would error out.
sorry, no such luck, just didn't pick up any new lines
What is the format of inserted_date in SQL?
It is returning as text 28/01/2016 15:50:30 ...... the SQL field is a standard date time format from a mysql database.
Have you tried running this query directly in my sql:
SELECT collectdate,
cslsupplierref,
easyWebref,
`export_stamp`,
`inserted_date`,
`lock_stamp`,
ownerid,
`processed_stamp`,
`qc_reason`,
`qc_stamp`,
`qc_user`,
`snap_ref`,
stu
FROM snap.shipmentlog
Where `inserted_date`>'28/01/2016 15:50:30';
Does this run and give you the result as you would expect?
Interestingly, No - it doesn't It doesn't pick up any of the lines
Where inserted_date>'28/01/2016 15:50:30';
Exactly, so you need to figurewhat format works?
May be try this is SQL and see if this works or not
Where `inserted_date`>'
2016-01-28 15:50:30
';
or this:
Where inserted_date>'
2016-01-28 15:50:30
';