Hi Qlikers,

Sometimes we can have requirement to calculate the business working hours between two Dates due to certain reasons. There are many solutions available on community but I thought let's create the document so that everyone can utilize this.

Consider the below case

Let's say one Ticket is logged into the system on certain day , call it as Start Date, and the same Ticket got resolved on certain day, call it as End Day. Now we may want to calculate the working hours between these two Dates to calculate the efficiency of ticket closure time.

1) Calculate business working hours excluding week ends(Saturday and Sunday) and Holidays.

Below are the considerations

1) Count only standard working hours ( 9 AM - 6 PM)

2) Exclude Saturdays and Sundays

3) Exclude Holidays

Below is the Setup in QlikView

You can change the Date format of the below variables as per your convenience eg. 'DD/MM/YYYY hh:mm:ss' to 'DD-MMM-YYYY hh:mm:ss'

SET TimeFormat='hh:mm:ss';
SET DateFormat='DD/MM/YYYY';

SET TimestampFormat='DD/MM/YYYY hh:mm:ss';

First, convert your Timestamp date format to 24 hour format using the below functions

a) Timestamp# ()    (if your Timestamp values are text) 

b) TimeStamp ()     (If your Timestamp values are in proper Timestamp format)

Now you can setup the below variables for standard working hours in weekdays.You can change the Variable according to your working hours (Here 9 AM - 6 PM) and rest calculation will be done automatically

Now Setup the Holiday list as below. You can use your own holiday list

Below is the logic to calculate the business working hours between to dates

Here is the output

2) Calculate business working hours excluding week ends(Saturday and Sunday), Lunch Breaks and Holidays.

Below are the considerations

1) Count only standard working hours ( 9 AM - 6 PM)

2) Exclude Saturdays and Sundays

3) Exclude Lunch Break (1 PM - 2PM)

4) Exclude Holidays

Set the Variables for standard working hours and lunch breaks. You can change the values according to your needs

Include the Holidays

Below is the logic to calculate the business working hours between two dates

Here is the output

Please see the attached QVW.

Feel free to provide any suggestions.

Regards,

Kushal

Hi All,

This document helps you in loading multiple excels and excel sheets with the name of the sheet and data.

//to read each file from the specified folder

FOR EACH file in FileList('filepath\*.xlsx');

//In order to get the file information from SQLtables command making use of the ODBC connection format
ODBC CONNECT32 TO [Excel Files;DBQ=$(file)];

tables:
SQLtables;
DISCONNECT;

FOR i = 0 to NoOfRows('tables')-1
LET sheetName = purgeChar(purgeChar(peek('TABLE_NAME', i, 'tables'), chr(39)), chr(36));
Table:
Load * ,
FileBaseName()as FIle,
FileDir() as Dir,
FileName() as File_Name,
'$(sheetName)' as Sheet_name
From $(file)(ooxml, embedded labels, table is [$(sheetName)]);

NEXT i

Next

Hope this helps !!!

please find the attachment for the eg: qvw and test fiels

Regards,

Avinashelite‌

I have two tables Table1,Table2. Table1 has DATE, Table2 has START_DT,END_DT. Want to join these two tables on DATE columns.Intervalmatch function will help us to make the join in Qlik. If we use left join, we can avoid synthetic tables too. Here is the example.If you want to test, prepare the test data.
Let me know if you need any more info/clarification.
Table1:
LOAD COUNTRY,
DATE,
C1,
C2
FROM Table1;
Table2:
LOAD COUNTRY, C3,C4,
date(START_DT) as START_DT,
date(END_DT) as END_DT
FROM Table2;
Final_Table:
INTERVALMATCH (DATE) left JOIN LOAD START_DT, END_DT RESIDENT Table2;
left Join (Table1) LOAD distinct * RESIDENT Table2;
DROP TABLE Table2;

We were looking for a solution to update Excel file pivottables before they are loaded into QlikView. I came up with the macro script below, which I want to share with you. I added an extra option to run the Workbook_Open macro. When opening an Excel file via QlikView, this macro will not start (to my experience). Choosing action option 2 will run the macro from QlikView, therefore starting whatever process is started when you open the Excel file manually.

In QV press Ctrl+M to add the macro to the QV document. Be sure to allow system access for this macro function to work. Check the example script in the header of the function. Be aware that the filename should be quoted with single quotes.

Please click the Like link if you are going to use this macro.

' *****************************************************
' Description: Function to open an Excel document and
'              refresh all external data queries and
'              pivottables in the Excel document or
'              run the Workbook_Open macro that runs
'              when manually opening the Excel document.
' Parameters:
' fileName > Specify the Excel document path to open.

'            The path should be the absolute (complete) path on the filesystem.
' action   > 1 = Open Excel file and refresh all external data tables.
'          > 2 = Open Excel file and run Workbook_Open macro.
'
' Example:
' LET result = OpenExcel('<path>\filename.xlsx', 1);
' If $(result) Then
'      LOAD * FROM '<path>\filename.xlsx';
' EndIf
' *****************************************************
Function OpenExcel(fileName, action)
Dim objExcel, objWb, fso

    ' Get file extenstion from the document name
    ext = Mid(fileName, InStrRev(fileName, ".") + 1)

    ' Check if specified document name is an Excel document
    If ext <> "xlsx" And ext <> "xlsm" And ext <> "xls" Then
        OpenExcel = False
        Exit Function
    End If

    ' Create a filesystem instance
    Set fso = CreateObject("Scripting.FileSystemObject")
    ' Exit the function if the documentdoes not exist
    If Not fso.FileExists (fileName) Then
        OpenExcel = False
        Exit Function
    End If

    ' Create an Excel instance
    Set objExcel = CreateObject("Excel.Application")
    ' Exit the function if Excel object cannot be created. Excel is not installed?
    If objExcel Is Nothing Then
        OpenExcel = False
        Exit Function
    End If

    ' Disable Excel prompts and alerts
    objExcel.DisplayAlerts = False

    ' Open the Excel document
    Set objWb = objExcel.Workbooks.Open(fileName)
    ' Exit the function if document cannot be opened. Is the document already opened?
    If objWb Is Nothing Then
        OpenExcel = False
        Exit Function
    End If

    ' Do some stuff according to the specified action parameter
    Select Case action
    Case 1
        ' For Excel 2007 and later documents refresh all external data sources
        ' and pivottables in the document
        If ext = "xlsx" Or ext = "xlsm" Then
            objWb.RefreshAll

        ' For Excel 2003 document refresh each pivottable on each sheet
        ElseIf ext = "xls" Then
            ' First refresh each external query table on all sheets.
            ' Pivottables may depend on these query tables
            For Each sh in objWb.Worksheets
                For Each qt In sh.QueryTables
                    qt.Refresh
                Next
            Next
            ' Refresh every pivottable on all sheets
            For Each sh in objWb.Worksheets
                For Each pvt In sh.PivotTables
                    pvt.RefreshTable
                Next
            Next
        End If

    Case 2

        ' Run the Workbook_Open macro
        objWb.Application.Run "ThisWorkbook.Workbook_Open"
    End Select

    ' Save Excel document and quit Excel
    objWb.Close True
    objExcel.Quit

    ' Cleanup memory
    Set fso = Nothing
    Set objWb = Nothing
    Set objExcel = Nothing

    ' Return true if the function finishes without an error.
    ' This does not mean that the Excel refresh was succesful
    OpenExcel = True
End Function

Generic Keys is a way to define keys between tables in a more general way so that their values can represent other things than individual key values; they can represent groups of key values or any key value. As an example, you can combine product IDs, product group IDs and a symbol for all products into one key field.

You can use generic keys to solve many data modeling problems:

• Authorization table with OR-logic between fields
  If you have an authorization table you sometimes want to have a slightly more complex access restriction than a simple logical AND between fields. It could be e.g., that a user is allowed to see sales for all regions for a specific product and at the same time the European sales for all products. Generic keys can be used here.
• Mixed dimensional granularity in a single fact table
  Often you want to compare actual numbers with budget numbers. The standard method is to concatenate these two tables into one common fact table. However, this new fact table could have mixed granularity in many of the dimensions. Generic keys can be used here.
• Multiple fact tables linked using a master link table
  Sometimes you have fact tables that are so different that you don’t want to concatenate them. To solve this problem you can make a data model that has a central link table and uses generic keys.

See more in the attached files.

HIC

PS I have been asked to make an example on comparing budget and actual numbers, so here it comes as a second attachment. It is a zip file with a mock-up order database with a budget. Create a new empty qvw; expand the zipped files in the same folder as the qvw; insert one of the two included script files into the qvw and run the script.

Hi All,

The below script helps to remove the empty columns (which has all null values) dynamically from the DataModel.  To use just copy the below script and paste at the end of your script.

FOR a = 0 TO NoOfTables() - 1
    LET vTable = TableName($(a));
    LET d = 0;
    FOR i = 1 TO NoOfFields('$(vTable)')
        LET j = i - d;
        LET vField = FieldName($(j), '$(vTable)');
        LET vFieldValueCount = Alt(FieldValueCount('$(vField)'), 0);
        IF (vFieldValueCount <= 0 ) THEN
         DROP FIELD [$(vField)] FROM $(vTable);
         TRACE DROP FIELD [$(vField)] FROM $(vTable);
         LET d = d + 1; // dropping fields impacts the internal field no.
        END IF
       NEXT i
    NEXT a

If you want the empty values (whitespaces) then use below script

FOR a = 0 TO NoOfTables() - 1
  LET vTable = TableName($(a));
  LET d = 0;
  FOR i = 1 TO NoOfFields('$(vTable)')
  LET j = i - d;
  LET vField = FieldName($(j), '$(vTable)');
  DropField:
  LOAD 1 as Check2
  Resident $(vTable)
  WHERE Len(Trim($(vField))) > 0;

  LET vNoOfRows = NoOfRows('DropField');
  DROP TABLE  DropField;
  IF vNoOfRows = 0  THEN
  DROP FIELD [$(vField)] FROM $(vTable);
  TRACE DROP FIELD [$(vField)] FROM $(vTable);
  LET d = d + 1; // dropping fields impacts the internal field no.
  END IF       
  NEXT i
NEXT a

You can test the above script using below Inline table.

Data:

LOAD

*,

' ' AS Field3,

Null() AS Field4

INLINE [

Field1, Field2

1,2

3,4

5,6];

When you use the first scrip then Field4 will be dropped, and when you use second script then both Field3 and Field4 are dropped.

Hope this helps.

Regards,

jagan.

Introduction

By design, QlikView uses OR condition when multiple values are selected in a List object. That means when a user select more than one value in a List, QlikView will execute an OR operation, associating values from associative model from any value selected from List. However, sometimes the user needs to evaluate an AND condition. For example, from a Movies Database, who are the actors has worked together? Of course if a user select two or more actors from the List, QlikView will evaluate this selection as an OR operation. The challenge is change this behavior. So, this post is designed to teach you how to change load script to turn on this option from a List object. If any List object has its properties accessed, from General tab is possible to see an option to turn on the AND MODE. But, this option is disabled and can not be selected if some procedures aren't executed at script level.

To test this procedure just import data from Movies dashboard, a file installed as a sample with QlikView Desktop. To use data from this document just execute the command Binary as shown on the following example. Of course you need check if the file is present in the path indicated. Binary command ins't necessary to use AND MODE and this post will use it just to load some data as sample. Pay attention to insertBinary in first line of script.

Binary ; 

Changing Data Model to Use AND MODE

To use AND MODE in a List, data from different tables needs to be loaded as unique values. For example, we know that an actor maybe has worked in two or more movies. And a movie has typically many actors. To enable AND MODE on a List is necessary create a table using actors and movies as unique pairs. For that, the DISTINCT instruction will be used at script level. This will allow create a new table with IDs from both actors and movies, like image at side.

Remember that by default when an actor is selected, all other values are not associated. Selected value is shown using green color while associative values from other lists keeps white. What we want is keep white in the same list, all actors that has worked together, that means, on same movie. Even if an actor is selected, when AND MODE is turned on, all actors with common movie will keeping white color. To enable this behavior is necessary create a new table with values from actors and movies. But, in this new table only distinct pairs of values should be inserted.

Using this concept, go to script editor pressing CTRL + E shortcut. In last line (empty one) use the following instruction to create a new table named ANDActor. Don't worry about how to load actors or movies data, because binary statement will load all tables before you use it. The Load Distinct statement will create a new table using distinct combinations from both fields (FilmID and Actor). The Resident instruction refers to load data from a previous table loaded before. It's like a FROM statement in a SQL command.

If you open the Table Viewer before write the statement below, you will be able to see original tables loaded by Binary command. The actor table has two fields, actor and film. If the same actor has worked in different movies, his or her name will be replicated.

ActorsAND:

Load Distinct

FilmID,

Actor As ANDActor

Resident Actors;

As a result a new field named ANDActor is now available. Let's go to add lists to main interface of QlikView document. Title has movies names. Check the next section how to configure Lists to use AND MODE

Setup Lists for AND MODE

Now your model is ready to use AND MODE and can be configured accessing List properties clicking with right button over ANDActor List object. Basic on distinct values loaded in the new table, now it's possible select AND MODE from General tab. Remember that only this field is available to enable AND MODE functionality because only this field was configured with unique pair of values. If you try change this same feature using another list, this option will be disabled and couldn't be selected.

To test this behavior, select a actor from the list. Behold that other values in the list will remain white. Why? Because any white values mean that actors had worked together. For example, if you select Harrison Ford from ANDActor list, a lot of white rows keeps showing. All of them worked together in one or more movies. But, scrolling down is possible to see actors names in gray, since some of them never worked with Harrison Ford. More a thing different can be noticed: The ampersand signal is shown.

When selecting some titles from films list is possible to see all actors that had worked together on this movie. That's a normal procedure. But, after clear all selections try to select Harrison Ford and Bradley Gregg. Use CTRL key to select both of then. Is possible to see that these two actors worked together in Indiana Jones and the Last Crusade. But, not only this. All other names in white color from actors list worked together. More one resource is available while using AND MODE. It's a NOT mode.

Another way to use AND MODE is clicking over a value in the List and hold mouse button up to cell color changes to strong red. For example, the user would like to know movies where Harrison Ford worked but Angela Staines not. First, clear all selections and after that click on Harrison Ford name. Hold CTRL key while clicking over Angela Staines name. Keep mouse button down up to change cell colors and release button followed by CTRL key. Results should be something like picture below. So, answer for user is a list of star wars movies.

In the other hand, selecting both Harrison Ford and Angela Staines while not using NOT procedure, will show films where both actors worked together. In this case, both names are painted in green. In the Title list is possible to check other movies.

Need More?

www.qknow.com.br

Often when building QlikView applications, or picking up applications which have been built by someone else I want to have a quick and easy way of viewing the data that is in that application.  To enable me to do this I have put together a page of objects that lists all tables and fields in the data model and then gives outline information about any selected field.  These objects can be copied and pasted into any QlikView application to view the data model of that document.

I have documented how this document works and the reasons why you might use it in a blog post here:

http://www.quickintelligence.co.uk/qlikview-data-profiler/

Please see the blog post for further details on using this document.

There is now a Qlik Sense version of this app, which you can find on Qlik Community here:

Qlik Sense App: Generic Data Profiler

I hope that you find it useful.  You will find other applications that I have uploaded under my profile on QlikCommunity, or on our Downloads Page.

Steve

http://www.quickintelligence.co.uk/qlikview-blog/

‎

PLEASE NOTE: The 'With Mask' version of the file includes an experimental tab that may or may not work well on large data sets.  Please see comment below for details.  If you are not sure which to download go for DataProfiler.qvw.  Thanks!

Hi, all of my Qlik applications have QVD data staged and prepared in advance. Here I will go through how I import them efficiently into QlikView.

For now I’ll assume you’re extracting your data each day and incrementally storing them to monthly QVD files.

• Fact 2014-01.qvd
• Fact 2014-02.qvd
• Fact 2014-03.qvd
• Fact 2014-04.qvd
• Fact 2014-05.qvd
• Fact 2014-06.qvd

Lets say the requirement for the dashboard is to have the last two months of complete data plus the current month to date. So if today were the 15th of June 2014 the Dashboard should contain data from 1st April to 15th June 2014.

You could load the data like this (replacing the dates to min / max variables and use dollar expansion):

Issues with this approach:

• We’re using a wildcard on the QVD filename so we’re attempting to load from files we know there’s no corresponding dates.
• The where clause will also prevent the QVDs being an optimized load.

This is how I would tackle the requirement.

1. First I’d load %KeyDates from my Calendar (http://community.qlik.com/docs/DOC-6593) using the flags to select the date range required
2. I’d then load a curser table containing the QVD names I need to load
3. Looping through the curser table loading the data using a where exists on the %KeyDates loaded in the first step

1) Load %KeyDates

Here I am loading from my Calendar QVD (you can find this resource on my blog Calendar with flags making set analysis so very simple | qlikcentral and on the Qlik Community http://community.qlik.com/docs/DOC-6593).

The resulting table has all the %KeyDate(s) for the time period in numeric format. You need to have an identical field in the FACT data as we’ll be using this field in the where exists statement.

We use the QVDLoadSuffix field in the next load statement

2) Load a Curser Table containing QVD Names

Here I create a new table with distinct values

This tell us the QVD files we need to look at when loading the data

3) Looping through the curser table

Here we perform a loop reading each row of ‘QVD_Files_To_Load’ (in this example it had three rows as shown earlier; 2014-04, 2014-05 and 2014-06)

The Field Value ‘QVDLoadSuffix’ is read into a variable and that is used within the filename of the QVD we’re loading from.

The where exists ensures only those %KeyDates already loaded (in the first step) are brought back. You can argue on this example the where clause is not required and you’d be correct. I’ve added it to show how we tackle both of the original challenges and it certainly would be required if you were loading the last 90 days (three months) from the current date.

And Finally….

It’s worth noting you can only load optimized with one where exits clause. You can’t use “where exists(field1) and exists(Field2)” without loosing performance although you could combine the fields in your data.

I’ve assumed the Fact Data has the same fields and therefore it will be automatically concatenated. If your data isn’t consistent you could create an inline or autogenerate a table with the complete field list and one row of null values before entering the loop and then concatenate to that. That will also ensure your loads stay optimized (There a useful post which talks about optimization - http://community.qlik.com/docs/DOC-2641)

You can also pass from the QMC a variable that tells the application how many dates to load in Step 1. For example you may want to create a light version with only three days worth of data. Putting Step 1 within a conditional IF statement you can very simply control the load behavior. (I’ll post more about this technique soon)

As always I’d love to hear your comments

Richard

qlikcentral | Understand / Create / Inform.

This article is about converting the GK (Gauss-Krüger) coordinates into WGS 84 (Latitude/Longitude)

Background:

In cartography, the term Gauss–Krüger Geographic coordinate system is named after Carl Friedrich Gauss (1777-1855) and Johann Heinrich Louis Krüger. It is a particular set of transverse Mercator projections (map projections) used in narrow zones in Europe and South America, at least in Germany, Turkey, Austria, Slovenia, Croatia, Macedonia, Finland and Argentina. This Gauss–Krüger system is similar to the universal transverse Mercator system (UTM), but the central meridians of the Gauss–Krüger zones are only 3° apart, as opposed to 6° in UTM. Depending on the zone of the Gauss-Kruger coordinates, different EPSG codes are applicable (The country of Germany is within the EPSG-Codes 31466 to 31469). Nevermind about the EPSG code, the below conversion works on any Gauss-Kruger coordinates.

The typical Gauss-Krüger coordinates is a pair of "Rechtswert" (East) and "Hochwert" (North) coordinates, both numbers are 7 digits long, may have up to 3 decimals (since the unit is meter, decimals don't make much sense as they are about decimeters, centimeters, even millimeter).

Links:

• An online translater example is here:  WGS84 L�nge/Breite oder Gau�-Kr�ger nach MGRS :: Geograph Deutschland
  It supports even http-get requests.
• I would not have been able to solve this without this How-To Excel. http://www.kram.de/pca/koordinatenumrechnung.xls Thanks to the author!
• http://en.wikipedia.org/wiki/Gauss%E2%80%93Kr%C3%BCger_coordinate_system
• DE:Gauß-Krüger - OpenStreetMap Wiki (in German)

Solution:

Enough about the background, to convert given set of Gauss-Krüger coordinates from a given "Rechtswert" (East) and "Hochwert" (North) in QlikView add the following script fragments. Adjust the values of XCOORD_Field and Y_COORD Field with the effective column names in which the GK values are found in the later LOAD statement.

// constants

LET XCOORD_Field = 'XKOORD';

LET YCOORD_Field = 'YKOORD';

// Formulas
SET @rho = 57.29577951; //=180/PI

SET @e2 = 0.006719219;

SET @b1 = "($(YCOORD_Field)/10000855.7646)";

SET @b2 = "Pow($(@b1),2)";

SET @bf = "325632.08677*$(@b1)*((((((0.00000562025*$(@b2)-0.0000436398)*$(@b2)+0.00022976983)*$(@b2)-0.00113566119)

    *$(@b2)+0.00424914906)*$(@b2)-0.00831729565)*$(@b2)+1) / 3600/ $(@rho)";

SET @fa = "(($(XCOORD_Field)-(Floor($(XCOORD_Field)/1000000)*1000000)-500000)/(6398786.849/Sqrt(1+(Pow(COS($(@bf)),2)*$(@e2)))))";

SET @LAT_Formula = "($(@bf)-(Pow($(@fa),2)*(Sin($(@bf))/Cos($(@bf)))*(1+(Pow(COS($(@bf)),2) * $(@e2)))/2)

    +(Pow($(@fa),4)*(Sin($(@bf))/Cos($(@bf)))*(5+(3*Pow(Sin($(@bf))/Cos($(@bf)),2))+(6*(Pow(COS($(@bf)),2)

    * $(@e2)))-(6*(Pow(COS($(@bf)),2) * $(@e2))*Pow(Sin($(@bf))/Cos($(@bf)),2)))/24)) * $(@rho)";

SET @LON_Formula = "(($(@fa)-(Pow($(@fa),3)*(1+(2*Pow(Sin($(@bf))/Cos($(@bf)),2))+(Pow(COS($(@bf)),2)

    * $(@e2)))/6)+(Pow($(@fa),5)*(1+(28*Pow(Sin($(@bf))/Cos($(@bf)),2))+(24*Pow(Sin($(@bf))/Cos($(@bf)),4)))/120))

    * $(@rho)/COS($(@bf))) + (Floor($(XCOORD_Field)/1000000)*3)";

Now if you import a file or table with Gauss-Krüger coordinates in fields XKOORD / YKOORD this is your script (dark-blue part). If you plan to use QlikView's built-in Mapping Extension "Quick Map" or "QlikView Mapping Extension", the coordinates-pair needs to go into one field, which I am calling LON_LAT. They need to have US number format. use the dark-red part of the script as well.

SET US_Format = "'','.',' '";

LOAD

    *

    ,Num(LON,$(US_Format)) & ',' & Num(LAT,$(US_Format)) AS LON_LAT   

;

LOAD

     ID

     ,XKOORD,

     ,YKOORD

     ,$(@LAT_Formula) AS LAT

     ,$(@LON_Formula) AS LON

FROM

    [myExcel.xlsx]

    (ooxml, embedded labels);

Enjoy,

Christof

Find the attachement to implement google maaps in qlikview. Documents with screenshots are avail. Attached the QVW file also for your reference. Regards. Siva Sankar k

I've re-edited the document  and fixed the  issue. Regards, siva sankar

This has been created with help from multiple questions and answers on the forum and some extra vb searches. Hopefully, others will be able to adapt this for other purposes as required.

Requirements

• I needed to create set variable actions as field triggers for a complex QVD creator user interface I have made.
• I wanted to be able to have the creation and deletion of these triggers automated due to the large number of fields.
• I also wanted to have both OnChange and OnSelect triggers for these fields.
• The variable values are the possible values of the fields separated by hashes. This is used by my QVD creator load script (that's for another day).

My real field names have been replaced with generic text for this posting. In this example the field names I am using have a similar naming structure of LIST_GROUP1_NAME1. The variables have a similar naming structure of vd_SELECTIONS_GROUP1_NAME1. I wanted to be able to have multiple arrays to loop through these levels.

sub create_field_triggers

'turn on msgboxes for testing

v_msgboxes = 0

'systems

array_systems = Array("GROUP1","GROUP2","GROUP3","GROUP4")

For s = lbound(array_systems) to ubound(array_systems)

'fields

array_fields = Array("NAME1","NAME2","NAME3","NAME4","NAME5","NAME6","NAME7","NAME8","NAME9","NAME10")

For f = lbound(array_fields) to ubound(array_fields)

if v_msgboxes = 1 then msgbox "LIST_" & array_systems(s) & "_"& array_fields(f) end if

'remove existing onchange actions

set fld=ActiveDocument.GetField("LIST_" & array_systems(s) & "_"& array_fields(f)) 

set prop = fld.GetProperties 

set actions=prop.OnChangeActionItems 

if v_msgboxes = 1 then msgbox "LIST_" & array_systems(s) & "_"& array_fields(f) & " Count Before Delete OnChangeActionItems = " & actions.count end if

for i=actions.Count-1 to 0 step -1

actions.removeat(i) 

fld.setproperties prop

next  

if v_msgboxes = 1 then msgbox "LIST_" & array_systems(s) & "_"& array_fields(f) & " Count After Delete OnChangeActionItems = " & actions.count end if

'remove existing onselect actions

set actions=prop.OnSelectActionItems 

if v_msgboxes = 1 then msgbox "LIST_" & array_systems(s) & "_"& array_fields(f) & " Count Before Delete OnSelectActionItems = " & actions.count end if

for i=actions.Count-1 to 0 step -1

actions.removeat(i)

fld.setproperties prop 

next

if v_msgboxes = 1 then msgbox "LIST_" & array_systems(s) & "_"& array_fields(f) & " Count After Delete OnSelectActionItems = " & actions.count end if

'create onchange actions

set actions=prop.OnChangeActionItems

if v_msgboxes = 1 then msgbox "LIST_" & array_systems(s) & "_"& array_fields(f) & " Count Before Add OnChangeActionItems = " & actions.count end if

actions.Add

i = prop.OnChangeActionItems.count-1

actions(i).Type = "31"

actions(i).Parameters.add

actions(i).Parameters(0).v = "vd_SELECTIONS_" & array_systems(s) & "_"& array_fields(f)

actions(i).Parameters.add

actions(i).Parameters(1).v = "='#'&CONCAT(DISTINCT LIST_" & array_systems(s) & "_" & array_fields(f) & ",'# #')&'#'"

fld.SetProperties prop

if v_msgboxes = 1 then msgbox "LIST_" & array_systems(s) & "_"& array_fields(f) & " Count After Add OnChangeActionItems = " & actions.count end if

'create onselect actions

set actions=prop.OnSelectActionItems

if v_msgboxes = 1 then msgbox "LIST_" & array_systems(s) & "_"& array_fields(f) & " Count Before Add OnSelectActionItems = " & actions.count end if

actions.Add

i = prop.OnSelectActionItems.count-1

actions(i).Type = "31"

actions(i).Parameters.add

actions(i).Parameters(0).v = "vd_SELECTIONS_" & array_systems(s) & "_"& array_fields(f)

actions(i).Parameters.add

actions(i).Parameters(1).v = "='#'&CONCAT(DISTINCT LIST_" & array_systems(s) & "_" & array_fields(f) & ",'# #')&'#'"

fld.SetProperties prop

if v_msgboxes = 1 then msgbox "LIST_" & array_systems(s) & "_"& array_fields(f) & " Count After Add OnSelectActionItems = " & actions.count end if

next

next

end sub

Other actions types can be created instead. I was using the below script to check the action type codes for actions that already exist on a field.

sub check_trigger_types

'field name to be checked

set fld=ActiveDocument.GetField("LIST_GROUP1_NAME1")

set fp = fld.GetProperties

set actions=fp.OnSelectActionItems

for i=0 to actions.Count-1

msgbox "ID: " & i & chr(10) & "Type: " & actions.item(i).Type & chr(10) & "Parameter 1: "& actions.item(i).Parameters(0).v & chr(10) & "Parameter 2: "& actions.item(i).Parameters(1).v

next

end sub

Have you ever created a P&L statement in QlikView?  Well, this technical brief outlines how to create a P&L statement in QlikView 11 using IntervalMatch.

Also check out this blog post.

Thanks,

Jennell

Below script helps in generating the Master calendar.  In this script I am getting the Minimum and Maximum dates in my Invoice table and generating the entries for each date in CalendarMaster table between minimum and maximum invoice date.

CalendarMaster:

LOAD

    Date(InvoiceDate) AS InvoiceDate,

    Year(InvoiceDate) AS Year,

    'Q' & Ceil(Month(InvoiceDate) / 3) AS Quarter,    

    Month(InvoiceDate) As Month,

    Day(InvoiceDate) As Day,

    Week(InvoiceDate) As Week;

Load Date(MinDate + IterNo() -1 ) AS InvoiceDate While (MinDate + IterNo() - 1) <= Num(MaxDate);

Load

    Min(InvoiceDate) AS MinDate,

    Max(InvoiceDate) AS MaxDate

RESIDENT Invoice;

Hope this helps others in generating the Master Calendar.

Regards,

Jagan.

Sub Print

  PrintPDF("Your File Name","RP01")

End Sub

Function PrintPDF(NamePDF, qvObject)

  Dim vPath

  vPath= "C:\"

  Set PDFCreator = CreateObject("PDFCreator.clsPDFCreator")

  PDFCreator.cStart ("/NoProcessingAtStartup")

  PDFCreator.cOption("UseAutosave") = 1 ' Enable autosave

    PDFCreator.cOption("UseAutosaveDirectory") = 1 ' Use a specific save directory

  'Comment this line if you already set on PDFCreator a directory where you wont to save a PDFfile

  'PDFCreator.cOption("AutosaveDirectory") =vPath  ' Set autosave directory from variable  'ActiveDocument.getVariable("TempPath").GetContent.String ' Set autosave directory from set analisis variable

    PDFCreator.cOption("AutosaveFormat") = 0 '  0=PDF, 1=PNG, 2=JPG, 3=BMP, 4=PCX, 5=TIFF, 6=PS, 7= EPS, 8=ASCII

    PDFCreator.cOption("AutosaveFilename") = NamePDF ' & ".pdf"' set the filename, optionally you can get the Caption of the object and use it as Filename

    PDFCreator.cPrinterStop = False          'printer enabled

  ActiveDocument.PrintDocReport qvObject, "PDFCreator"    'print

  ActiveDocument.GetApplication.Sleep 5000    'stop my macro for 5 sec, its time is need to save a PDF

  PDFCreator.cClearCache()    'clear cache

  PDFCreator.cClose() 

end function

One of our Qlik Community Buddy (Rupinder Singh) shared an awesome way to optimize the performance of group by statement but I don't think this got enough lime light which it should have. In order to give it better exposure, I am creating this document from this thread Optimize Group By

For below I am using QV version - 11.2 SR4

I have always had trouble with aggregating my data as "Group by" statements in scripts have always taken too long for my comfort.

For example, to group by  23 million records (2 columns - ID and Sales) it takes me 10 mins.

Table_Group:

Load ID,Sum(Sales)

Resident Table

Group By ID;

Instead of this, If I Order by my data by ID and then run the same Group by clause, I get results backs in less that 2 mins.

Table_Sort:

Load ID,Sales

Resident Table

Order by ID;

Table _Group:

NoConcatenate Load ID,Sum(Sales)

Resident Table_Sort

Group By Table_Sort;

I would have believed QV to internally perform this step, but explicitly implementing this has improved my query times by 500%.

I have tables with 40 columns (36 of which to be grouped by and 4 to be summed) and the query time has gone from 18 to 6 mins.

I have just ensured that my Order By column list is the same as Group By Column List.

So, If Order by is "Order By Column1,Column2,Column3" then Group by is "Group By Column1,Column2,Column3"

I would love to hear back if others in the community also have faced similar challenges and whether this or any other method has helped them improve Group by Performance.

Hi Folks,

Recent days,I have seen some of them are asking on running Qlikview applications through windows scheduler in community.

Generally we follows 3 tier architecture in our project.

Assume we have 3 qvw files.

1.Raw QVD file generator (Raw_QVD_App.qvw)

2.Model QVD file generator (ModelQVD_App.qvw)

3.Qlikview Application.(QV_Application_Name.qvw)

We have QV_Application_Name.qvw file run reload the data daily in C:\QlikviewApplications\ and copy into destination folder C:\ProgramData\QlikTech\QlikView\Documents\

Here is the batch file script

"C:\Program Files\QlikView\qv.exe" /R "C:\QlikviewApplications\Raw_QVD_App.qvw"
"C:\Program Files\QlikView\qv.exe" /R "C:\QlikviewApplications\ModelQVD_App.qvw"
"C:\Program Files\QlikView\qv.exe" /R "C:\QlikviewApplications\QV_Application_Name.qvw"
copy "C:\QlikviewApplications\QV_Application_Name.qvw" "C:\ProgramData\QlikTech\QlikView\Documents\"


Exit

We can schedule by using windows task scheduler to run this batch file daily/weekly/monthly,...

Please review the script.

Best Regards,

QW

Below is a script that was developed to quickly pull the fields from a list of tables into QVDs and perform incremental loads on the QVDs going forward.  This particulate script was created to pull data from Saleforce.com however it can easily be adapted for other sources as well.  The script depends on the primary key having the same name between all tables.  Instructions for using the script are included in the comments.

//  Commets with ************ indicate locations where the script needs to be customized for your use



//************Add Connection String below *************************


//**********************************************************************
Set vQVDPath = QVDs/; //****************  Enter Location to save QVDs in the path should end it back slash.  For example: C:\MyQVDs\
                                                                                                              //                                         For realtive paths are also possible.  For example   QVDs\  will put the QVDs in a subfolder from where the QVW is saved.

//Set the variables so the last time of script execution is known
SET vLastExecTime          = 0; // resetting vLastExecTime
LET vLastExecTime          = timestamp(if(isnull(QvdCreateTime('$(vQVDPath)ReloadHistory.QVD')), 0, ConvertToLocalTime(QvdCreateTime('$(vQVDPath)ReloadHistory.QVD'), 'GMT', 1)), 'YYYY-MM-DD hh:mm:ss');
LET vExecTime                  = timestamp(UTC(), 'YYYY-MM-DD hh:mm:ss');
LET vTodaysDate     = today();


//For the 1st reload, this section will be skipped.
LET FirstReload = isnull(QvdCreateTime('$(vQVDPath)ReloadHistory.QVD'));

if Not $(FirstReload) then
// Read Reload History Data
ReloadHistory:
Load
No,
[Last Reload Ended],
[Reload Started]
FROM $(vQVDPath)ReloadHistory.qvd (qvd);

end if

ReloadHistory:
LOAD
RowNo() as No,
'$(vLastExecTime)' as [Last Reload Ended],
'$(vExecTime)' as [Reload Started]
Autogenerate(1);

//ReloadHistory.qvd will keep track of when the last reload occurred for incremental loading purposes. If you need to reload all the data then delete ReloadHistory.qvd from your file structure.
                STORE * FROM ReloadHistory INTO $(vQVDPath)ReloadHistory.qvd;

//*****************Add a list of tables to be loaded from your data source below.  Keep the first row "TableName".  This is the name of the field.
TablesList:
Load * Inline [
TableName
Account
Opportunity
];

For i = 0 to (NoOfRows('TablesList')-1);
Let varTableName = Peek('TableName',$(i), 'TablesList');

// Load Tables
                $(varTableName)_SQL:
SQL Select *
FROM $(varTableName)
WHERE LastModifiedDate >=$(vLastExecTime) and LastModifiedDate < $(vExecTime);   //***************************Change the "LastModifiedDate" to the name of the field for the last modified data time stamp in your tables.
               
// For the 1st reload, this section will be skipped.
                if Not $(FirstReload) and not isnull(QvdCreateTime('$(vQVDPath)$(varTableName)_SQL.qvd')) then

//CONCATENATE ($(varTableName))
$(varTableName)_SQL:
Load *
FROM $(vQVDPath)$(varTableName)_SQL.qvd (qvd)
WHERE NOT EXISTS(Id);   // *************************************** Change "Id" to the primary key for your tables (note all your tables must use the same name for the primary key for this script to work.
               
end if

// If table exists then proceed to the next step
                if NoOfRows('$(varTableName)_SQL') > 0 then

STORE $(varTableName)_SQL INTO $(vQVDPath)$(varTableName)_SQL.qvd;

DROP TABLE $(varTableName)_SQL;

End if
Next;

Drop Table TablesList;

A really simple script I must_include at the beginning of all my scripts to load global variables, managed in a seperate place (an Excel file in the example below, could of course be just as well SQL.

Any comments of course greatly appreciated.

// ---------------------------------------------------------------------------
// Script to load global variables from Excel file and store into variables
// ---------------------------------------------------------------------------


// ---------------------------------------------------------------------------
// Load global variables from Excel file into a table
// ---------------------------------------------------------------------------


Variables:
LOAD Name, 
     Value 
FROM
[QlikView variables.xlsx]
(ooxml, embedded labels, header is 1 lines, table is variables);


// ---------------------------------------------------------------------------
// Storing the global variables into variables
// ---------------------------------------------------------------------------


Let RowCount = NumMax(NoOfRows('Variables'),0)-1;
For i=0 to '$(RowCount)'
  Let TempVarName = peek('Name',$(i),'Variables');
  Let TempVarValue = peek('Value',$(i),'Variables');
  Let $(TempVarName) = '$(TempVarValue)';
Next


// -------------------------------------------------------------------------------------------
// A bit of housecleaning, we won't be needing the temporary variables as well as the table
// -------------------------------------------------------------------------------------------


Let TempVarName = null();
Let TempVarValue = null();
Let RowCount = null();
DROP TABLE Variables;

Generally, QVD's are one of the most important in any Qlikview deployment.

A QVD (QlikView Data) file is a file containing a table of data exported from QlikView. QVD is a native QlikView format and can only be written to and read by QlikView. The file format is optimized for speed when reading data from a QlikView script but it is still very compact. Reading data from a QVD file is typically 10-100 times faster than reading from other data sources.

Whenever we are are using to QVD as data source, we need to keep the Optimized load better reload times. But in some cases we didn't keep optimized loads especially if we are using filters with Where command.

Other hand , we can keep optimized loads in some scenarios:

LOAD * FROM QVDANAME.qvd (qvd) Where Match(REGION_NAME,'US','CANADA');

In the above case, we are loading the data only from US and CANADA region and this is unoptimized load. But we can load as Optimized load by using EXISTS key word with temp table.

First, create the Temp table with filters and use EXISTS key word in main QVD loading statement like below:

TEMP:

LOAD * INLINE [

REGION_NAME

US

CANADA ];

LOAD * FROM QVDANAME.qvd (qvd) Where EXISTS (REGION_NAME) ;

DROP Table TEMP ;

Note: Please make sure that TEMP having the same field name which QVD have