topic Re: Convert sql query to equivalent script in QlikView in QlikView

Convert sql query to equivalent script in QlikView

Wed, 15 Aug 2012 06:52:05 GMT

Hello,

I'm migrating an application in WPF to QlikView. So far it has been a smooth process except migrating a histogram. Currently we use sql queries to generate the points for the histogram. The query used looks like the one below.

Select ROUND(SomeNumberColumn/1000000, 1) as 'Number in millions', NULLIF(COUNT(SomeNumberColumn/1000000),0) as Frequency from v_myView where TradeDate between @startdate and @enddate GROUP BY ROUND(SomeNumberColumn/1000000, 1) HAVING COUNT(SomeNumberColumn/1000000) <> 0 ORDER BY ROUND(SomeNumberColumn/1000000, 1)

Note.

1. I have already loaded all the data in QlikView from the view named v_myView from the database (At load)

I do not want to make another query to the same view to generate the histogram values. I want to get it done at the QlikView script level. Can somebody convert the above sql to qlikview script?

Thanks,

Ranjith

Re: Convert sql query to equivalent script in QlikView

IAMDV — Wed, 15 Aug 2012 07:08:48 GMT

Hi Ranjith,

You don't need to convert this script into QlikView syntax. You can directly create OLEDB connection and you can use your T-SQL Queries.Although QlikView doesn't understand T-SQL statment but your OLEDB connection understands the T-SQL statements. So you can run your queries and OLEDB connection retrieves the data from DB and then QlikView will load the data in to the memory.

Please add the word "SQL" as prefix before the "Select" word. I hope this makes sense and let me know if you neeed futher help. Something like this...

OLEDB CONNECT TO [Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False; ..................................]

SQL Select

ROUND(SomeNumberColumn/1000000, 1) as 'Number in millions',

NULLIF(COUNT(SomeNumberColumn/1000000),0) as Frequency

from v_myView

where TradeDate between @startdate and @enddate

GROUP BY ROUND(SomeNumberColumn/1000000, 1)

HAVING COUNT(SomeNumberColumn/1000000) <> 0

ORDER BY ROUND(SomeNumberColumn/1000000, 1);

Just change the @startdate and @enddate variables and also do you really need ORDER BY clause? This will force SQL Server Engine to perform table scan instead of any potential seeks.

Good luck!

Cheers,

www.QlikShare.com

Re: Convert sql query to equivalent script in QlikView

Wed, 15 Aug 2012 07:12:33 GMT

Hi Deepak,

Thanks for the reply. As I mentioned, I already created an OLEDB connection and wrote a SQL query to load all data from my view. Now, I do not want to make another SQL call to the db. Instead I want to make use of the already loaded data and generate the two new columns for the histogram. Not sure if I'm making sense but just in case.

Cheers,

Ranjith

Re: Convert sql query to equivalent script in QlikView

IAMDV — Wed, 15 Aug 2012 07:33:22 GMT

Ah! gotcha. Sorry I didn't read that properly...my bad!

So you wanted to perform transformations in QlikView because you already loaded the data once. This makes sense and you need something like this...

//Step 1: Using Resident Load we are loading the data from your View which is already in the Memory

NoConcatenate

v_myView2:

Load

ROUND(SomeNumberColumn/1000000, 1) as 'Number in millions',

COUNT(SomeNumberColumn/1000000) as SomeNumberColumnCount,

IF(IsNull(COUNT(SomeNumberColumn/1000000)) = -1,0, COUNT(SomeNumberColumn/1000000)) as Frequency

Resident v_myView

where TradeDate >= $(startdate) and TradeDate <= $(startdate);

//Step 2b: We are using preceeding load to replicate the Having Clause. So we are first using the Group BY clause and then restricting the grouped data set.

NoConcatenate

Final_v_myView:

LOAD *

where SomeNumberColumnCount <> 0;

//Step 2a: We are using Group BY clause

LOAD

[Number in millions],

SomeNumberColumnCount,

Frequency

Resident v_myView2

GROUP BY [Number in millions];

//Step 3:Droping the Temp table from QlikView

Drop Table v_myView2;

There might be syntax errors but this is the logic and I hope it makes sense!

Cheers,

www.QlikShare.com

Re: Convert sql query to equivalent script in QlikView

Wed, 15 Aug 2012 08:07:54 GMT

This is exaclty what I wanted. Thanks DV.

But now, when I execute the above script I get an error saying "Invalid expression" for "Step 1 query". Any idea what I might be missing?

Cheers,

Ranjith

Re: Convert sql query to equivalent script in QlikView

IAMDV — Wed, 15 Aug 2012 08:18:18 GMT

You are welcome Ranjith.

Like I said the above script was the direction for you to understand the logic/approach and I haven't tested the script and just wrote it as plain english. It will be good if you can post the sample QVW file? I can mock up an example for you.

Cheers,

www.QlikShare.com

Re: Convert sql query to equivalent script in QlikView

Wed, 15 Aug 2012 08:23:47 GMT

Hi DV,

I have attached the qvw.

Re: Convert sql query to equivalent script in QlikView

IAMDV — Wed, 15 Aug 2012 13:09:44 GMT

Ranjith,

I'll get back to you soon.

Cheers,

www.QlikShare.com

Re: Convert sql query to equivalent script in QlikView

IAMDV — Sat, 18 Aug 2012 16:50:04 GMT

Ranjith,

Have you got this working? Sorry I was busy all week. Let me know if you need help.

Cheers,

www.QlikShare.com

Re: Convert sql query to equivalent script in QlikView

Thu, 23 Aug 2012 02:51:54 GMT

Hi DV,

Yes, I got it working. Thanks for the help.

Cheers,

Re: Convert sql query to equivalent script in QlikView

IAMDV — Thu, 23 Aug 2012 10:25:21 GMT

RC,

That's great! Please post ur answer and mark the thread to "answered". It helps other users while searching.

Thanks,

Re: Convert sql query to equivalent script in QlikView

Mon, 27 Aug 2012 03:57:41 GMT

Here is the link to the answer.

http://community.qlik.com/message/249313#249313

Cheers