Thursday, February 2, 2017

Generate dynamic script using Qlikview Script

Having to use Qlikview for around 1-2yrs, I find that Qlikview script is quite easy to pick up for those developers who have the basic programming foundation. Having to work on QV in my job, I have discovered 'hacks' that you can use qv scripts to eg. generate dynamic scripts, manipulate directories, creating ETL and many more.

One of the ways I am going to show you is to be creative and do something that is out of the box.

Generate dynamic ftp script.

NoConcatenate myscript:
LOAD * Inline [
# MyCodeTitle
open sftp://mysite.com,
cd myremotedir,
]
;

LOAD 'get c:\myloaddir' AS '# MyCodeTitle' AutoGenerate 1;

LOAD * Inline [
# MyCodeTitle
bye
]
;

STORE myscript into [C:\script.txt] (txt);

What I did is to load an inline script to create 1 column cannot be more than 1) name as # MyCodeTitle which will be shown as remarks on the generated script. The column cannot be more than 1 as it will be tabulated.

The middle part is where the magic happens.
LOAD 'get c:\myloaddir' AS '# MyCodeTitle' AutoGenerate 1;
This part of the code can be dynamic and you can actually create a for loop wrapping around this code if you need to read data from somewhere.

and Finally
LOAD * Inline [
# MyCodeTitle
bye
]
;STORE myscript into [C:\script.txt] (txt);
You end your ftp script by concatenating your previous dataset and save it as your ftp script. script.txt.

Now you can open your script and it should look like exactly like an ftp script.
It's that easy! And remember to drop table after storing it!