I have my SQL query working using MicrosoftSQL Server management Studio - I can get it to produce a CSV file (but without headers) - I can hard code them in - but QlikView does not pick up all the rows?
I import an SQL - get rid of all the "" but then I have problems with QlikView not recognising a function.
What is the best way forward?
Is thee a document that I can read anywhere?
Best would be directly connect QlikView with your SQL database , like below:
1) Create the odbc in control panel (windows) and set the database
2) go to qlikview, edit script and use the button "Connect"
3) put the user and password
4) go to the button "Select"
5) all the table will be appear
If it's a normal Transact SQL query, you can use it in the QlikView script.
If you're running into an issue of functions that are available in SQL, but can't be used in QlikView, you can either try and replicate - using a QlikView function or create a view in SQL and read that in as a table.
The function is one which works from within the MS SQL Server environment. Not standard sql functions as such.
I am wondering if the best thing to do would be to get the sql server to spit out a table each night and then get QlikView to work on the table using the odbc connect as suggested by Amit. Then QlikView can be automated to run daily as well.
That means my problem is with MS SQL Server – I need to get it to save a table/view/csv file I can use?
Meanwhile I can copy/paste the output of the query from MS SQL Server into Excel and work on that. Sounds like a good plan or a waste of time?
Can you view the results in SQL Management Console by creating a view?
If so, you can load the view into QlikView as if it were a table.
That way you can still use the function in SQL but don't have to have the extra step of writing to a file and reading from it with another application (QlikView).