topic Re: COUNT() in SQL Script not Working in QlikView

COUNT() in SQL Script not Working

Fri, 05 Feb 2016 18:34:27 GMT

Hi There, I'm trying to figure out what I may be doing wrong with a Qlik file that seeks to perform counts in a temp table via SQL. The script is longer than what I have posted below, but I'm able to get this to return results in a different query application.

When I add it into my qlikView document, it doesn't throw an error, but it also doesn't return results.

I've been able to narrow it down to the COUNT() function, but I don't understand exactly WHY this isn't working in qlikView... is it a reserved keyword it's not compiling properly, or something else?

SELECT ROW_NUMBER() OVER(PARTITION BY t.Record_ID ORDER BY t.Order_Placed) AS rowNumb,

t.Record_ID,

t.Order_Shipped,

t.Value

INTO #treatmentSummary

FROM #treatments AS t

WHERE t.Section_Field_Name = 'Scheduled Treatment #'

SELECT d.Record_ID

, COUNT(ts.RowNumb) OVER(PARTITION BY d.Record_ID) as No_of_Orders

INTO #temp_orderCount

FROM #Data AS d

LEFT JOIN #treatmentSummary AS ts

ON d.Record_ID = ts.Record_ID

When i replace the above red text with

, 1 as No_of_Orders

the code runs.

I know there are some dependent tables missing in the above queries, but does anyone know why this is happening, and how I might be able to get around it? Thanks in advance for your insight!

Re: COUNT() in SQL Script not Working

Fri, 05 Feb 2016 21:42:00 GMT

You can't use your already loaded table on other SQL statement.

Are you executing any procedure in Qlikview ? Please share your script ?

Re: COUNT() in SQL Script not Working

marcus_sommer — Sat, 06 Feb 2016 08:06:45 GMT

Quite often it's helpful to make your transformations within qlikview and not within the sql-part which will be executed by the database, see: Preceding Load.

- Marcus

Re: COUNT() in SQL Script not Working

Mon, 08 Feb 2016 18:38:34 GMT

Thank you Marcus; I'm not sure that will be possible in this case. The temp table has a lot of dependencies to other elements in the SQL script.

I figured out a workaround for this instance, but I'm still just trying to understand WHY this wouldn't work in Qlik. Count() is a pretty common function; why wouldn't Qlik be able to handle it within the SQL code?

Re: COUNT() in SQL Script not Working

Mon, 08 Feb 2016 18:43:18 GMT

Thanks Dathu, and sorry if I'm being unclear. This is all being handled in SQL code, of which COUNT() is a valid function. I'm not 100% sure what you mean by "executing procedures in Qlik", but the entirety of the SQL code is being executed in Qlik via an OLEDB connection.

The script itself should be fine; it executes in other querying tools like Management Studio; I'm trying to understand why it doesn't execute in Qlik while not throwing any errors.

Re: COUNT() in SQL Script not Working

marcus_sommer — Mon, 08 Feb 2016 19:43:33 GMT

It hasn't anything to do with qlikview if a sql-statement didn't worked then qlikview don't execute the sql - the order is the following: qlikview connects to your database and gives the sql-statement to the odbc/oledb-driver which communicate between database and qlikview which will only return the result of the query.

In your case it could be that your odbc/oledb isn't appropriate for your database respectively to your kind of sql-statement - many driver provides only old standard-sql and not quite new and/or specialized sql-features.

- Marcus

Re: COUNT() in SQL Script not Working

maxgro — Mon, 08 Feb 2016 20:05:36 GMT

Regarding this statement

SELECT d.Record_ID

, COUNT(ts.RowNumb) OVER(PARTITION BY d.Record_ID) as No_of_Orders

INTO #temp_orderCount

FROM #Data AS d

LEFT JOIN #treatmentSummary AS ts

ON d.Record_ID = ts.Record_ID

I would check if the 2 # tables have data before this statement and if the left join between the 2 tables gives some result.

I would check what happens removing the INTO .......; do you get some rows in Qlik?

Also, maybe you can replace the COUNT....OVER with a group by?