Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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
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
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
Hi DV,
I have attached the qvw.
Ranjith,
Have you got this working? Sorry I was busy all week. Let me know if you need help.
Cheers,
DV
Hi DV,
Yes, I got it working. Thanks for the help.
Cheers,
RC