Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

where & dollar sign expansion

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);

1 Solution

Accepted Solutions
sunny_talwar

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)';

View solution in original post

12 Replies
MK_QSL
MVP
MVP

Try

Where inserted_date>'$(MaxID)';

sunny_talwar

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.

Not applicable
Author

sorry, no such luck, just didn't pick up any new lines

MK_QSL
MVP
MVP

What is the format of inserted_date in SQL?


Not applicable
Author

It is returning as text 28/01/2016 15:50:30 ...... the SQL field is a standard date time format from a mysql database.

sunny_talwar

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?

Not applicable
Author

Interestingly, No - it doesn't It doesn't pick up any of the lines

MK_QSL
MVP
MVP

Where inserted_date>'28/01/2016 15:50:30';

sunny_talwar

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