topic Re: where & dollar sign expansion in QlikView

where & dollar sign expansion

Thu, 28 Jan 2016 15:27:18 GMT

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

Re: where & dollar sign expansion

MK_QSL — Thu, 28 Jan 2016 15:28:32 GMT

Try

Where inserted_date>'$(MaxID)';

Re: where & dollar sign expansion

sunny_talwar — Thu, 28 Jan 2016 15:30:06 GMT

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.

Re: where & dollar sign expansion

Thu, 28 Jan 2016 15:48:25 GMT

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

Re: where & dollar sign expansion

MK_QSL — Thu, 28 Jan 2016 15:50:21 GMT

What is the format of inserted_date in SQL?

Re: where & dollar sign expansion

Thu, 28 Jan 2016 15:50:31 GMT

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

Re: where & dollar sign expansion

sunny_talwar — Thu, 28 Jan 2016 15:53:36 GMT

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?

Re: where & dollar sign expansion

Thu, 28 Jan 2016 15:56:39 GMT

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

Re: where & dollar sign expansion

MK_QSL — Thu, 28 Jan 2016 15:58:18 GMT

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

Re: where & dollar sign expansion

sunny_talwar — Thu, 28 Jan 2016 16:00:47 GMT

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

Re: where & dollar sign expansion

Thu, 28 Jan 2016 16:15:53 GMT

Perfect!

All i need to do now is get my dollar sign expansion to return the date with single quote marks.

I've reformatted it but i get this error: I think I'm missing those quote marks

SQL##f - SqlState: 37000, ErrorCode: 1064, ErrorMsg: [MySQL][ODBC 5.3(w) Driver][mysqld-5.1.73]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '16:15:23' at line 15

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`>2016-01-28 16:15:23

Re: where & dollar sign expansion

sunny_talwar — Thu, 28 Jan 2016 16:19:38 GMT

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

Re: where & dollar sign expansion

santharubban — Thu, 28 Jan 2016 16:25:13 GMT

Can you try

MaxID = Date(Floor(Datefeild));