Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
Author

11 Replies
IAMDV
Luminary Alumni
Luminary Alumni

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
Author

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
Luminary Alumni
Luminary Alumni

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,

DV

www.QlikShare.com

Not applicable
Author

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
Luminary Alumni
Luminary Alumni

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
Author

Hi DV,

I have attached the qvw.

IAMDV
Luminary Alumni
Luminary Alumni

Ranjith,

I'll get back to you soon.

Cheers,

DV

www.QlikShare.com

IAMDV
Luminary Alumni
Luminary Alumni

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
Author

Hi DV,

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

Cheers,

RC