Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Not applicable

Convert sql query to equivalent script in QlikView

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

1 Solution

Accepted Solutions
Not applicable

Re: Convert sql query to equivalent script in QlikView

Here is the link to the answer.

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

Cheers

RC

11 Replies
IAMDV
Honored Contributor II

Re: Convert sql query to equivalent script in QlikView

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,

DV


www.QlikShare.com

Not applicable

Re: Convert sql query to equivalent script in QlikView

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

IAMDV
Honored Contributor II

Re: Convert sql query to equivalent script in QlikView

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 3Smiley Very Happyroping 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,

DV

www.QlikShare.com

Not applicable

Re: Convert sql query to equivalent script in QlikView

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

IAMDV
Honored Contributor II

Re: Convert sql query to equivalent script in QlikView

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,

DV

www.QlikShare.com

Not applicable

Re: Convert sql query to equivalent script in QlikView

Hi DV,

I have attached the qvw.

IAMDV
Honored Contributor II

Re: Convert sql query to equivalent script in QlikView

Ranjith,

I'll get back to you soon.

Cheers,

DV

www.QlikShare.com

IAMDV
Honored Contributor II

Re: Convert sql query to equivalent script in QlikView

Ranjith,

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

Cheers,

DV

www.QlikShare.com

Not applicable

Re: Convert sql query to equivalent script in QlikView

Hi DV,

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

Cheers,

RC

Community Browser