Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

COUNT() in SQL Script not Working

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!

6 Replies
Not applicable

Re: COUNT() in SQL Script not Working

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

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

Not applicable

Re: COUNT() in SQL Script not Working

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?

Not applicable

Re: COUNT() in SQL Script not Working

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

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

MVP
MVP

Re: COUNT() in SQL Script not Working

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?

Community Browser