Qlikview: Highlighting selected dimensions

If there are too many data points (or lines, or bars, etc) displayed in a chart, the user might want to highlight the ones which (s)he is interested in while still keeping the others in a lose color to be able to make comparison.

This can be done as follows:

1. Let user make selections in dimensions
2. Get selected values of each dimension into a separate variable when a button (e.g HIGHLIGHT) is clicked
3. If a dimension value of a chart do not exists in the values kept in the variable, then color it as light gray. This can be done by background color setting.

Here is a link to the example qlikview application: Highlight.qvw

Please, open the highlight.qvw, make a couple of selections in list boxes and click on Highlight Selected button and observe the highlighting.

Qlikview: How to send a report by email from access point

Step 1)
Create a report on a qlikview application which has some dummy data. (e.g. sales report in the attached qlikview file.)

Step 2)
Create a field in the qlikview applications to keep recipient email address. (e.g. recipient field in the attached qlikview file.)  and display it in a listbox.

Step 3)
Create a variable in the qlikview application to keep a string which will contain recipient's email address and the name of the output PDF file separated by semi colon.  (e.g vPdfName2 variable in the attached qlikview file.)

Step 4)
Create a listbox in the qlikview application which will display the content of the variable.

Step 5) 
Create a button in the qlikview application that will call the following macro:


Sub PrintRpt()
        'print the report and get the reponse back in a variable
set printResponse=ActiveDocument.PrintReportEx("RP01")

'get the name of the PDF file created by the print action above
sURL=mid(printResponse.URL,3)

        'get the email address of the recipient selected in the recipients listbox
set recipient=ActiveDocument.fields("Recipient").GetSelectedValues
set email=recipient.Item(0)

        'set the concatenated string (email address and name of the PDF file) as content of the listbox created in step 4
ActiveDocument.Variables("vPdfName2").SetContent chr(39) & email.text & ";" & sURL & chr(39), true

'set the path of the folder in which the text file will be crated
sFile="C:\_QVW\PDFfolder\WatchThis\email_" & replace(sURL,".pdf","") & ".txt"

        'Export the content of the listbox into a text file that contains the email address and name of the PDF file
set lb = ActiveDocument.GetSheetObject("LB05")
lb.ServerSideExportEx sFile , ";" , 1 '0=HTML, 1=Text, 2=Bitmap, 3=XML, 4=QVD, 5=BIFF
End Sub


Step 6)
Write a powershell script that can read a text file to get email address and path of the pdf file and send it by email.

Following posts have related examples:
how to loop through files in a folder by windows powershell
How to send email by windows powershell

Step 7) 
Schedule a windows task which will monitor the folder for incoming files and trigger the powershell script to read the file's content and send email with the PDF attached.

Following post have a related example:
How to monitor a folder and trigger an action for incoming files, in Windows 7
The only difference from the above example will be the source defined in the XML definition of the event filter. Instead of  "C:\windows\explorer.exe", we should now have "C:\Program Files\QlikView\Server\QVS.exe", because the text file will be created by qlikview server.

Test:
Deploy the qlikview application on the server and open it from access point and click on the button to see the result.

Example File: email_report.rar

How to loop through files in a folder by windows powershell

Step 1:
Create a powershell file named as loopfiles.ps1 (you can simply create a new text file and rename it as loopfiles.ps1).

Step 2:
Open the loopfiles.ps1 file and paste the following and save it:

############################


$fso = new-object -com scripting.filesystemobject
$folder = $fso.getfolder("C:\temp")

foreach ($f in $folder.files) {
    $f.path >> filesvisited.txt
$c = Get-Content $f.path
}

############################

Step 3:
Right click on the loopfiles.ps1 and select Run with Powershell.

At the end, one should have an output file (filesvisited.txt) with the names of the files in the current folder.

How to send email by windows powershell

The example below is built to run on gmail accounts.

Step 1:
Create a powershell file named as send_email.ps1 (you can simply create a new text file and rename it as send_email.ps1).

Step 2:
Open the send_email.ps1 file and paste the following and save it:


function fnSendEmail($Too,$Filee)
{
$smtpServer = "smtp.gmail.com"
$att = new-object Net.Mail.Attachment($Filee)
$msg = new-object Net.Mail.MailMessage
$smtp = new-object Net.Mail.SmtpClient($smtpServer, 587)
$smtp.EnableSsl = $true
$smtp.Credentials = New-Object System.Net.NetworkCredential("your-email@gmail.com", "your-password");
$msg.From = "your-email@gmail.com"
$msg.To.Add($Too)
$msg.Subject = "powershell test"
$msg.Body = "This is a test email with an attachment"
$msg.Attachments.Add($att)
$smtp.Send($msg)
$att.Dispose()
}

Step 3:

• Start windows powershell 
• change the directory to where the send_email.ps1 file is located (pls see the line 1 in the screenshot below)
• call send_email.ps1 file (pls see the line 2 in the screenshot below), like that, one can use the fnSendEmail function in it
• call fnSendEmail function by passing to parameters (recepient's email address and the full path of the file to be attached) (pls see the line 3 in the screenshot below)

How to monitor a folder and trigger an action for incoming files, in Windows 7

Step 1:

Copy a file into a folder and look for the details of this event in windows event viewer. For example, if  copy a file named test - Copy.txt in C:\_QVW\PDFfolder\WatchThis folder, I found the following entry in the windows event viewer:

Make note of the event id (4656) and keyword (Audit Success) and that we are looking at security windows logs.


Step 2:
Go to the windows task scheduler and click on create task.

Step 2.1:
In general tab, name your trigger as My Folder Monitor as follows:

Step 2.2:

In triggers tab, click on New. In New Trigger window, set begin the task to On an event and select custom in settings, then click on New Event Filter button. In New Event Filter window, set the options as follows:

Note that we set the options according to what we have found in step 1.

Click OK and OK to finish this step.

Step 2.3:

In the action tabs, click on New. In the New Action window, set the options as follows:

Click OK and OK to finish this step.

Step 3:

Now, we need to tell the task which folder to monitor. 

Step 3.1:

Go back to the windows event that we looked at in step 1. Open the event details by double clicking the event and take note of the details of the event. We can use ObjectName and ProcessName details which are under the EventData node to fine tune our scheduled task.

Step 3.2:

Go to triggers tab of the scheduled task and click on Edit and then edit event filter. Note that, it is opened in XML tab and one cannot go back to Filter tab. From now on, the event filter can only be modified in XML. 

Modify the XML expression to restrict the scheduled task to trigger the action only for the files in C:\_QVW\PDFfolder\WatchThis folder, as follows:

Please note that > means > (i.e greater than).

Now if you copy & paste or create a file in C:\_QVW\PDFfolder\WatchThis folder, a pop-up message should appear in the screen.

Hope it works on your side as well.

How to imitate two dimensional arrays in qlikview script

Below is a piece of qlikview script code which imitates two dimensional arrays:

2D array is supposed to be in the format like "MyArray" in the code below.
"GetItem" sub gets a item and assigns it to the given variable.
"FindItem" sub finds the item which corresponds to the given first dimension value and assigns it to the given variable.


Sub GetItem(fromArray,intoVar,X,Y)
Let firstDim=SubField(fromArray,',',X);
Let $(intoVar)=PurgeChar(SubField(firstDim,';',Y),Chr(39));
Set firstDim=NULL();
End Sub

Sub FindItem(fromArray,intoVar,SearchFor)
let k=fromArray;
For each t in $(fromArray)
Let s=t;
if SubField(t,';',1)=SearchFor then
Let $(intoVar)=SubField(t,';',2);
Exit for;
end if
next
End Sub


Set MyArray="'A;Turkey','C;Usa','D;Canada'";
Set vGetTest=Null();
Set vFindTest=Null();

Call GetItem(MyArray,'vGetTest',1,2); //This line sets 'vGetTest' to 'Turkey'
Call FindItem(MyArray,'vFindTest','C'); //This line sets 'vFindTest' to 'Usa'

Qlikview: Server: How to monitor a folder and trigger a QVS EDX task

I built a desktop windows application which monitors a certain folder and triggers an EDX task on qlikview server when a file is copied into the folder. The application also provides archiving option.

If you would like to try it yourself, please do the following steps:

1) Create an EDX task on the qlikview server:

2) Copy the EDX_Trigger_Desktop.exe and EDX_Trigger_Desktop.exe.config into the same folder, modify the values in the following entries of the config file according to your setup and run the exe file.
<add key="QMSAPIURL" value="http://localhost:4799/QMS/Service"/>
<add key="Taskname" value="EDXTEST"/>
<add key="TaskPassword" value="EDXTEST"/>
<add key="FolderToMonitor" value="C:\TEMP"/>
<add key="ArchiveFolder" value="C:\TEMP\ARCHIVE"/>

3) The application will display the values (folder to monitor, QMS URL, etc.) from configuration files. if necessary, these values can be modified manually in this screen:

Click the START button to start folder monitoring.

4) Copy a file into the folder which is being monitored and check if the EDX task is triggered on qlikview server.



The application and source files can be downloaded from the following links:

• Configuration and executable files:  release.zip
• Complete C# project:  EDX_Trigger_Desktop.zip

Facts, known issues, etc.:

• The application is developed with MS Visual Studio 2010 Express
• It triggers an EDX task on Qlikview Server version 11.