topic select and group by in QlikView

select and group by

Fri, 04 Nov 2011 16:54:50 GMT

Hi:

One simple question, why this script work in tsql (mssql) and not in qlikview

OLEDB

[Provider=SQLNCLI10.1;Integrated Security=SSPI;Persis ......

SQL

select

account,date1,date2,co1,cod2,sum(price)

from table

where account != '7777777701'

group by account, date1, date2, cod1, cod2

Any Idea???

Thanks, regards

César Estrada

select and group by

swuehl — Fri, 04 Nov 2011 17:05:57 GMT

Hi Cesar,

have you tried to use <> or not instead of != as comparison operator?

Regards,

Stefan

select and group by

Fri, 04 Nov 2011 17:22:39 GMT

Hi Stefan:

I Change != for <> and the error is the same

OLEDB read failed

without the group by, the sentece work fine

Thanks, regards

César Estrada

select and group by

rajeshvaswani77 — Fri, 04 Nov 2011 17:36:48 GMT

Hi Cesar,

You have put co1 as cod1 in group by.

Thanks,

Rajesh Vaswani

select and group by

rajeshvaswani77 — Fri, 04 Nov 2011 17:38:20 GMT

Hi Cesar,

You have put co1 as cod1 in group by.

Thanks,

Rajesh Vaswani

select and group by

Fri, 04 Nov 2011 17:42:04 GMT

Hi Rajesh:

Is true, but is not the error.

OLEDB

[Provider=SQLNCLI10.1;Integrated Security=SSPI;Persis ......

SQL

select

account,date1,date2,cod1,cod2,sum(price)

from table

where account != '7777777701'

group by account, date1, date2, cod1, cod2

Thanks!!

select and group by

rajeshvaswani77 — Fri, 04 Nov 2011 17:46:14 GMT

semicolon at the end?

select and group by

Fri, 04 Nov 2011 17:51:24 GMT

Hi:

the simicolon, exist in my scrip in qlickview, and the error is the same

Thanks!

select and group by

rajeshvaswani77 — Fri, 04 Nov 2011 17:53:55 GMT

Hi,

Can you post the script?

Thanks,

Rajesh Vaswani

select and group by

Fri, 04 Nov 2011 18:54:33 GMT

The script is

OLEDB

[Provider=SQLNCLI10.1;Integrated Security=SSPI;Persis ......

SQL

select

account,date1,date2,cod1,cod2,sum(price)

from table

where account != '7777777701'

group by account, date1, date2, cod1, cod2 ;

Thanks

select and group by

Thu, 10 Nov 2011 14:26:06 GMT

This is not a solution, but i can take the data, creating a view on the mssql and calling in my qlikview

Regards

select and group by

ToniKautto — Thu, 10 Nov 2011 22:15:47 GMT

The SELECT statement in your QlikView script is sent as written from QlikView to the database connector and then interpreted by the database. Generally if a database query does not function the problem would not be QlikView since it is not interpreted in QlikView. Please do the following steps to narrow down here it is going wrong.

1. Please make sure that you are using the same user in your QlikView connect statement as you are in your succesful test

2. Make sure the conenct statement works, by making a SELECT * for an arbitrary table

3. Try exactly the same SELECT statement from an other application like Excel, by using exactly the same connector

If the problem still remains, please specify what the sympthom of your issue is.

Do you get a error message, and what does it say?

Do you get incorrect data, specify expected and retrieved data?

select and group by

Fri, 11 Nov 2011 04:40:51 GMT

you can also do one more thing:

1. first load the table from sql.

2. then apply group by clause by making previously loaded table as resident.

try it.

regards