Since launching the AskQV site I have found myself using it as my first port of call when reminding myself of what someone had put in their blog post a while back. This has however meant that I have spotted a couple of items that no one has blogged on before. This post aims to rectify that for the use of the STORE statement to write a CSV.
More In STORE
Most QlikView developers will use the STORE statement frequently for writing QVD files during load scripts. If you don’t do this then you should probably read this previous post on the topic. What many people don’t realise though is that there other values that can appear in the brackets at the end of the statement, to define the format of the output.
CSV Files – Not Just For Reading In
Generally text files are things that we interface with for loading data in from other systems – perhaps after an automated extract. Sometimes, however, there is cause to send data from the data sources that you are bringing into QlikView into other systems – after crunching them in your load script. Perhaps these data are pushed externally via FTP or loaded via SSIS into a database. Often on Qlik Community there are people asking around how exports can be achieved by using macros on the front end in order to send the content of a Table to a text file. This can be necessary when the expressions in the Table need to be evaluated before export, or the users selection at the time of export is important. Often though the desired effect can be achieved in the load script without user interaction or potentially problematic macros (see this post by Henric Cronstrom for more on that topic).
The STORE Statement
As you will be aware, if you use STORE for writing QVDs, the STORE statement writes the contents of a single table in memory to a file during load script execution. Any required calculations need to be done during the LOAD statement, and if any of those calculations are aggreations then the load must include a GROUP BY statement. The syntax is very similar for CSV output:
STORE MyTable INTO ..\Ouptut\MyCSVFile.csv (txt);
As with when the statement is used to store to a QVD file; it is possible to specify only certain fields are written to the output file:
STORE Name, Address1, Address2, PostCode FROM MyTable INTO ..\Ouptut\MyCSVFile.csv (txt);
Unlike the QVD store, there are some optional parameters that can be given to further define the format of the file, such as:
STORE MyTable INTO ..\Ouptut\MyOutputFile.txt (delimiter is \t);
This will give you a tab delimited file, rather than a comma separated file (note that the extension of the file has no bearing on this, only the format specifier). There are other format specifiers that you can find in the help file or on Qlik Community.
Publishing to a Web Page with STORE
A neat little trick I have used on a few occaisions is to push output to a web page using the STORE statement. This can be simply done by writing out a HTML file during the load script. To see this in action simply try this in a QlikView load script:
MySource: LOAD RowNo() as Row, Rand() * 1000 as Value AUTOGENERATE(10) ; HTMLOutput: LOAD [<html>] INLINE [ <html> <head> <title>HTML Output</title> </head> <body> <h1>HTML Output Example</h1> <table border=1 cellpadding=5 cellspacing=0> <tr><td><b>Row Number</b></td><td align=right><b>Value</b></td></tr> ]; CONCATENATE(HTMLOutput) LOAD '<tr><td>Row ' & Row & '</td><td align=right>' & replace(num(Value, '#,##0.00'), ',', '-') & '</td></tr>' as [<html>] RESIDENT MySource; CONCATENATE(HTMLOutput) LOAD [<html>] INLINE [ <html> </table> </body> </html> ]; DROP TABLE MySource; STORE HTMLOutput INTO .\MyOutputFile.html (txt); DROP TABLE HTMLOutput;
What you will get is an HTML file that you could target at a web server to allow users to view values in a browser without needing to go via AccessPoint. Whilst the output from this example does not look particularly elegant you could incorporate a style sheet in the header to make the output far more attractive.
You could even go as far as using DIV blocks of colour with the dimensions of those blocks driven by the data to build a fully HTML based bar chart for inclusion on your site. I have used it to write to a wall board that refreshed every five minutes giving stats on the status of QlikView and details of any error conditions.
With the STORE statement in your QlikView kit bag, and a bit of inspiration, many things can be achieved.