Hey there,

I am starting this article to yell out to the community, that I have completed a generic script (suitable for QlikView and Qlik Sense using the Qlik REST Connector) to use the API of Synthesio for loading data.

I am not sure how many folks will search for this combination, so before I document hours of "how to do" here, please ping me a note (reply below or email me at christof.schwarz@gmx.at) that you're interested. I will then share it here. If noone ever asks for this combination, I saved a lot of time 😉

The idea is the following:

• Synthesio offers several REST APIs
• You have to get an accesstoken which is valid for some hours. My script is doing so.
• You can query Synthesio's Mention API, the one you likely look for are
  • the Reach method Synthesio API Reference and
  • the Analytics method Synthesio API Reference#
• The result is a json object, which unfortunately does not translate automatically into a workable datamodel when using the REST Connector wizard.
• I had to load the result basically into one cell (one row) and then parse the content accordingly.

Excerpt from the "magic script":

A QlikView Technology White Paper

Published: November, 2011

www.qlik.com

Introduction

Event Driven Execution (EDX) allows the running of QlikView batch jobs based on external events. The primary usage of EDX is to have an external scheduler or Extract,Transform, and Load (ETL) tool (for example, Informatica) run QlikView batch jobs as part of a larger batch process. There are also other use cases, forexample, triggering a QlikView batch when a file arrives and user-initiated batches. This document describes the requirements for each of these three use cases.

The example code (attached below as QMSEDX-v1.exe) and executable (attached below as QMSEDX_CommandLine_v1.exe) that is delivered together with this document uses the new EDX API, which means this document and the example code are useful both when using the EDX functionality for the first time and when migrating from previous versionsof the EDX API.

Use Cases

Using an External Scheduler or ETL Tool

Many large organizations have other batch requirements than just QlikView. Such organizations often use a standard tool to run all batch processes, so that the IT training costs are minimized and common operations (for example, logging and re-launching) are handled in one central place. In addition, QlikView batch processing is almost always reliant on new data being available and in many cases this data is created by other batch processes. An external scheduler or ETL tool can be used to prepare the data and then run the QlikView batch to load it.

Most scheduler and ETL tools are designed to run external batch programs via a command line interface. The reasons for this include:

• Batch processes are long-running, so starting aseparate process to handle them provides a simple way for the scheduler tomonitor the batch processes.
• The scheduler can be configured to run the batchusing a particular operating system account that provides the required securitycontext.
• If the batch process fails, the standard operatingsystem error handler can be used to pass this information back to the scheduleror ETL tool.

The main alternative to using a command line tool is using some sort of web services. Unfortunately,web calls cannot be used for long-running processes, since a timeout eventually occurs. A common solution to this problem is to start the process with a web request and then follow it up regularly with subsequent requests to find out if the original request has finished successfully or not.

File Arrival Detection

In many IT departments, the inter-system communication is not orchestrated by ETL or even Enterprise Application Integration (EAI) tools. Instead, files are pushed. For example, a non‑QlikView batch job that extracts data from a production database is typically owned by the IT team running the database, which means the team is responsible for creating and configuring the batch job and ensuring that it runs correctly.On the QlikView side, a program that checks if a file has arrived must be running, so that the file can be used in a subsequent QlikView batch.

In this scenario, simply starting the QlikView batch process should be enough. There is no need to follow its execution. The program is typically a Windows service as it runs all the time.

User-initiated Action

TheQlikView Automation API provides a number of functions that run QlikView batch scripts, for example, Document.Reload(). However, it is normally not a good idea to let users run batch jobs, since a QlikView batch job typically replaces the entire data set of an application used by many users.This means a single user can interfere with the work of all the others unless the QlikView batch is carefully configured. Because of this, the batch functionality is disabled in a QlikView Server deployment.

• The marketing department at a pharmaceutica lcompany has an organizational division between the analysts and the product managers. The analysts are in charge of loading a dozen external data sources of varying quality. Once the data relating to a particular product is ready, the running of a QlikView batch loads the data and publishes the application to the relevant product managers.
• A software company with thousands of employees wants each employee to have access to their sales data up to the last 30 minutes. The computer resources needed to support such data usage are massive. Since the salespeople only use the system once a week on average, it is decided to allow each user to run a QlikView batch to generate a QVW (for the user)whenever needed.

EDX in QlikView 11

In QlikView 11, EDX runs through the QlikView Management Service (QMS) API. This is a major change from QlikView 10, where EDX is realized by calls directly to a QlikView Distribution Service (QDS). The QMS API is a web service API that uses theSimple Object Access Protocol (SOAP). Client applications make HTTP (web) requests to QMS on port 4799. The system is secured by NT LAN Manager (NTLM) as well as special protective measures to avoid certain types of hijacking attacks known as “time limited service key”. This combination of security means the client application must be written in .NET and therefore the provided example code is .NET projects/solutions developed in Microsoft Visual Studio 2010.

The client application uses NTLM to authenticate a Windows account to QMS. QMS then checks which Windows groups the Windows account for the client application is member of to determine the function calls the user is allowed to make. Most of the QMS API requires membership in a local group called “QlikView Management API”, but to run EDX, a separate group, “QlikView EDX”, should be used. Both groups are local Windows groups on the server where QMS runs.

The client application makes calls to instruct QMS to start a task and in return receives an error code indicating success or failure, as well as an execution ID. The execution ID uniquely identifies the execution of the task as opposed to the task itself. The client application then periodically polls QMS to check the progress of the task execution. This poll request returns a data structure that contains, among other things, the execution status, start time, stop time (if already finished), and a list of new execution IDs. These subsequent execution IDs represent the execution of tasks that are triggered because the initial task has finished. The client application can then follow an entire set of inter-related tasks that together make up a full parallelized batch flow.

Lastly, the example code outputs log information and returns an error code to the operating system. The error code can be recovered in a standard way, for example, by using the %ERRORLEVEL% environment variable in a BAT file.

Changes Compared to Previous Versions

From a functionality perspective, the following has changed compared to using EDX in QlikView 10:

• Because communication is with QMS instead of QDS, a QDS cluster can be used to load balance EDX calls. If high availability is required for the batch environment, QMS is a single point of failure. Consider either virtualization of QMS or an active/passive system.
• The overall success or failure code of aQlikView batch can be returned. Previously, the status of the task could be fetched (that is, the definition of the batch job), but not the execution ofthe batch job.
• The execution of subsequent tasks can be followed.

Notes on Using the QMS API

Finally,some notes on using the QMS API in QlikView 11:

• The EDX password must not be null. Use an empty string, if no password is configured.
• The QDS ID is required in the function call that starts the EDX batch job. However, higher-level privileges than membership in “QlikView EDX” are required to get a QDS ID. The simple solution is to pass an empty GUID for the QDS ID, which means QMS will search for the task in all configured QDS instances. Almost all projects today only have one logical QDS configured, so this can be a useful solution.
• EDX can be used without a QlikView Publisher license. Simply pass the name of the QVW instead of a task name and the reloadschedule of the QVW will run.

Hierarchies are an important part of all business intelligence solutions, used to describe dimensions that naturally contain different levels of granularity. Some are simple and intuitive whereas others are complex and demand a lot of thinking to be modeled correctly.

• What is the difference between a balanced and an unbalanced hierarchy?
• How can a hierarchy be loaded into QlikView?
• Which data model should be used?
• How do I create an authorization scheme using a hierarchy?
• How can I check for data integrity problems in the hierarchy data?

These questions and others are answered in this Technical Brief. Attached you will also find a sample unbalanced, n-level hierarchy along with the script to load it.

See also the following blog posts:

Unbalanced, n-level hierarchies

Authorization using a Hierarchy

HIC

2013-11-26: Added section on authorization.

Hey,

I hope I can solve a lot of peoples headache with a better way of loading JSON data.

This method works by loading the JSON data into an table with a single field, and then placing it into a variable and then converting the value into xml which then is loaded. Works very well but is still a bit for the technical people. I will not provide any support.

In order to make this work, we need to detect the XML definition after JSON to XML conversion. Open the JSON Loader.qvw Application and the JSON url.

Copy the JSON output into the vData variable, and click the JSON to XML button. Now take the output in the left side and copy that into a .xml file and load it from qlikview.

The output script should look similar to this:

TAG:

LOAD adminCode3,

    adminName2

FROM [Desktop\xml.xml] (XmlSimple, Table is [XML/POSTALCODES/TAG]);

From this we need to extract 2 things. The list of fields (place into vFields), and the XML tree to load (place into vXMLTree).

In the edit script set the variables based on the above:

Let vUrl = 'http://api.geonames.org/postalCodeLookupJSON?postalcode=6600&country=AT&username=demo';

Let vFields = 'adminCode3,adminName2';

Let vXMLTree = 'XML/POSTALCODES/TAG';

Cross fingers and reload

Do this for each JSON source.

Enjoy Torben Seebach 4C Management Consulting

Hello everyone!

Higher management often requires specific data delivered to their mailbox every morning. They are not interested in interaction, using filters and “playing” with data. They just want their predefined Excel ready when they will turn on the PC. Of course great solutions like Qlik NPrinting are available, but there is a way to do it even with QlikView Personal Edition.

This document describes a methods for:

- Automated QlikView document reload

- Automated data extraction from QlikView to Excel file.

- Automated distribution of this Excel file to a specific recipient, using free Gmail account

- Scheduling and fully automating whole process with Windows Task Scheduler

You can find all files in the attachment.

TLDR: Learn how to fully automatically reload, extract and send data by email using Gmail account!

All inputs are welcome. I hope someone finds it useful!

PS. This document is based on one of mine QlikView blog posts.

BR,

Kuba

"How to discover your data in a fast dynamic way?"

When starting a new project the first thing I want to do is get a feel for the data. Preferably in a fast and dynamic way. You can use the "Advanced Data Discovery module" to tackle this problem. The Advanced Data Discovery module consists out of 4 dynamic list boxes and a dynamic table which comes in handy when modeling a project, checking data quality, testing the interaction between fields or exploring your data.

Screenshots:

Advanced Data Discovery sheet overview

Dynamic list box with field statistics

Benefits:

• Useful to check interaction between fields while creating your data model
• Dynamic Data Discovery
• Field statistics based on current selections or based on the complete set
• Fast data quality checks
• Dynamic list box

Cons:

• A script reload is required

PS: Special thanks to the creator of the first version of the dynamic list box from who I could not trace back the blog post I encountered 3 years ago.

The Advanced Data Discovery module is now part of the QvM - Qlikview Modules template.

Thanks for your feedback and comments.

Have fun Qlik'n, Koen

‌QlikViewModules.com

Hi everyone,

Here is a file to write the content of a QVD into an MS SQL database with minimum amount of coding.

The script uses bulk insert function in SQL and inserts 1,000 rows at a time.

There are instructions/assumptions in the front-end of the app.

1. Ensure the source QVD has the columns correctly formatted on the first run (as it creates the SQL table based on the coded Qlik data types - even though Qlik data types technically don't exist) as this determines the 'data types' it builds into the SQL table.
2. Replace the existing "CONNECT TO" string to the database server.
3. Point to the QVD you want to write to SQL.
4. Script will change/replace all special characters in data, table and field names. Underscore will replace any 'space' in table and field names and the latter 2 will be in 'all caps' to follow DB naming-convention.
5. Script will create a table in the database (the database itself must exist or be created prior to launching the script).
6. Re-run the script after the script has created the target SQL table and the script will (bulk) insert 1,000 rows at a time.
7. File creates/updates one "DB_Write_Log.qvd" QVD with stats of what has been written to the SQL table by this script/file (keeps a historical record).
   

Tentative stats are: (a) Writes 1,000 rows (one insert) every 1.63 seconds for a table 7 columns deep; (b) Writes 45,637 rows in 58 seconds for a table 10 columns deep.

Hope you find this useful.

A video tutorial here: Qlik Bulk INSERT into SQL table - YouTube

Have fun,

C  ;o)

This tutorial presents a script pattern for assigning time dimensions to multiple fact dates in a data model. It answers the commonly asked Forum question "how do I link to two dates"?

The pattern will demonstrate how to link all fact dates to a common calendar as well as using separate calendars for each fact date.

EDX is a trigger mechanism for starting a QlikView reload or task from an external source, such as a scheduler or application. To start a task using EDX the QlikView Management API must be used and this is delivered in the form of a Web Service.  The full details on EDX in QV 11 plus a detailed example and code is available here :  http://community.qlik.com/docs/DOC-2650

Below is a step by step simple example which may help you start to develop something for your own needs.  For this example you will require and need to be familiar with Visual Studio (the free version is fine, although the attached project is for Visual Studio 2010 pro) to write your application and you will need to under stand how to use QlikView management API. 

Step 1 - Create a task

First of all create the task you want to trigger, this can be any type of task and do this using the QlikView management account as normal.  For the task create a trigger, and for the type of trigger chose "On an external Event" and you need to specify a password that will be used to call the task (this isnt used anywhere else except for starting the task).  The below works both if you have a fully license Publisher but also if you just have server but the task creation screen in the QMC is slight different for each.

Step 2 - Create your application to us the API

We are going to use the QlikView management API to build an application that will start the task, the application itself could take any form such as web page with a button (like the attached) a command line app, a web service; whatever suits your business needs. The attached example is build as a .net web page with a button a user could click but the code can be lifted and moved to another application type. 

Its worth point out that this is the bare essentials for using EDX, it does not include things like task monitoring or error handling,  examples of more complex features can be seeing the above referenced document.

The attached project is pre-configured to use the QliKView Management API and below is just the code required to start a task,  refer to the documentation on how to get started with using the API as this can be a bit tricky to get used to.  I have used C# on a asp.net page and many examples will use this but you can use other languages and technologies also.

Open the attached project and you can review the key lines in the project by looking at the following:

First we will declare some variables we need to use,  the address of your QlikView server,  the name of your task (or its ID), and the password you specfied in the task.  Typically you would want to put these in a config file or allow them to be passed as parameters.

            string QMSURL = "http://localhost:4799/QMS/Service";

            string Taskname = "Reload of Golf Quest.qvw";

            string Taskpassword = "secret";

Second we will need to handle the Service Keys used to talk to the API, this needs to be done to call any function in the API, refer to docs for more details on this

            QMSClient Client;

            Client = new QMSClient("BasicHttpBinding_IQMS", QMSURL);

            string key = Client.GetTimeLimitedServiceKey();

            ServiceKeyClientMessageInspector.ServiceKey = key;

Finally the real part of the code example, calling the task.  It can be as simple as just one line as shown below or more complicated. 

            Client.TriggerEDXTask(new Guid("00000000-0000-0000-0000-000000000000"), Taskname, Taskpassword, "", new string[0]);

Breaking this line down the function "TriggerEDXTask" has 5 parameters

1. The GUID of the QDS - this isthe identity of the reload component in QlikView, this might be the basic one attached to a server or a full Publisher, its actually the same service.  With publisher the GUID might be something different to the blank one above however the above line should work in both cases. If you have them on different servers, or more than one publisher you need to give the full and correct GUID.
2. Taskname or GUID - fairly self explanatory the name of the task in the QMC, or its ID
3. Task Password is the password you specified in the task
4. Parameter name - When starting the task you can pass a variable into the reload script if needed, ive left it blank in this example
5. Parameter values - the values for the above.

OK thats it, of course there is plenty more that could be done here but this is the basics. It is also possible to monitor the task for completion.

Step 3 Security & Running your code

To be allowed to request something from the API, the user or the code calling the function needs to run as an account with particular permissions.  These are windows permissions defined on the QlikView Server as two local groups, users must be in either the QlikView Administrators or QlikView EDX groups. The later of these is not created by default.

Depending on what type of application you build the way the users name gets to the Management Service can vary.  If the code is built into a windows exe file this will run as the user currently logged into windows. If it runs as a web page it will by default run as the identify of the application pool in IIS depending on it is set up and this might not be correct. 

The attached example uses imponsation, that is that the ID of the user logged into the web page will be passed along to the QlikView API.  You can see this being turn on in the web.config file with the line  <identity impersonate="true"/>.  IIS needs to be configured to allow this to work by requiring windows authentication on the page and for the application pool to run in classic mode.  In this scenario every user who might call the function needs to be in the groups mentioned above, while this is quite granular it might be administation you dont want to have. Also if the application is being called by a non-windows source such as a Unix based scheduler this may give issues.

An alternative is to modify the code so that a single user name is used to connect to the API on the behalf of all users, of course you needs to factor in some way of ensuring only permitted users can then use the application.  To do this modify the code as follows.

After the line Client = new QMSClient("BasicHttpBinding_IQMS", QMSURL); place the following lines

            WindowsClientCredential creds = Client.ClientCredentials.Windows;

            creds.ClientCredential.UserName = "myadminaccount";

            creds.ClientCredential.Password = "mypassword";

This will now call the API using the hardcoded credentials, although these should be saved in a config file.

Good luck and i hope this example helps

HI All,

The below script helps in creating the Master Calendar with Working & Non working (Holiday) flags.  Holidays are loaded from the Excel file.

LET vWeeklyHolidays = 'Sat;Sun';   // Adjust weekly holidays here, currently considered Saturday & Sunday as holiday, based on your requirement change it accordingly

CalendarMaster_Temp:
LOAD
    Date(InvoiceDate) AS InvoiceDate,
    Year(InvoiceDate) AS Year,
    'Q' & Ceil(Month(InvoiceDate) / 3) AS Quarter,  
    Month(InvoiceDate) As Month,
    Day(InvoiceDate) As Day,
    WeekDay(InvoiceDate) AS WeekDay,  
    Week(InvoiceDate) As Week;
Load
     Date(MinDate + IterNo() -1 ) AS InvoiceDate  // Replace InvoiceDate with your actual date field name
While (MinDate + IterNo() - 1) <= Num(MaxDate);
Load
    Min(InvoiceDate) AS MinDate,
    Max(InvoiceDate) AS MaxDate    // Replace InvoiceDate with your actual date field name
RESIDENT Invoice;                  /// Replace Invoice with your actual Transaction Table.

LEFT JOIN(CalendarMaster_Temp)
LOAD Date(Holiday) AS InvoiceDate,
     Description AS HolidayDescription,
     1 AS IsHolidayFlag
FROM
[List of Holidays.xlsx]   // Load holiday details from Excel file attached, if you have this info in database tables change it accordingly
(ooxml, embedded labels, table is Holidays);  

Calendar:
LOAD
  InvoiceDate,
    Year,
    Quarter,  
    Month,
    Day,
    WeekDay,  
    Week,
    HolidayDescription,
  If(SubStringCount('$(vWeeklyHolidays)', WeekDay(InvoiceDate)) OR IsHolidayFlag = 1, 1, 0) AS IsHolidayFlag,
  If(SubStringCount('$(vWeeklyHolidays)', WeekDay(InvoiceDate)) OR IsHolidayFlag = 1,'Non Working/Holiday', 'Working Day') AS DayType
Resident CalendarMaster_Temp;

DROP TABLE CalendarMaster_Temp;      

Regards,

jagan.

A common metric with a business is to compare  current period with previous period a over same number of days.

few fall into mistake of comparison of current period with the whole previous period.  Take for example Month To Date Sales compared with Previous Month.   If the current date is 15 of the month the comparison is made over 15 days of the current month with 30 days of the previous month and this is not apple to apple comparison and the result will be on always down until reach a day of 30.  To overcome this I have created a master calendar to overcome the period comparison for WTD,MTD,QTD and YTD flags along with previous period Flags.

   if (TempDate >= monthstart($(vToday),-1) and TempDate <= monthstart($(vToday),-1)+ $(vToday)-monthstart($(vToday)) ,1,0) as PrMTDFlag,  //Prior Month Flag

in this Prior Month Flag is set over only same number of days for the current period.  Same follows for MTD vs PrMTD, QTD vs PrQTD and YTD vs PrYTD

The Script for creating an apple to apple Period Flags :

MinMax:

LOAD

  Min([Sales Date]) as MinDate,

  Max([Sales Date]) as MaxDate

RESIDENT [Sales];

LET vMinDate = Num(Peek('MinDate', 0, 'MinMax'));

LET vMaxDate = Num(Peek('MaxDate', 0, 'MinMax'));

LET vToday = $(vMaxDate);

//LET vToday = Num(Today(0));

/*************** Temporary Calendar *************

Generates a single table with one field containing

all existing dates between MinDate and MaxDate.

*/

//

TempCal:

LOAD

  date($(vMinDate) + rowno() - 1) AS TempDate

AUTOGENERATE

  $(vMaxDate) - $(vMinDate);

DROP TABLE MinMax;

/*************** Master Calendar ***************

Disconnected during the Date Island exercise by renaming TempDate as IslandDate

*/

SalesCalendar:

LOAD

// TempDate AS CalDate,

    TempDate AS [Sales Date],

  'Week-' & Week(TempDate) AS WeekDesc,

  Year(TempDate) AS Year,

  Month(TempDate) AS Month,

  Day(TempDate) AS Day,

  Weekday(TempDate) AS WeekDay,

   'Q' & ceil(month(TempDate) / 3) AS Quarter,

  makedate(year(TempDate),month(TempDate)) as MonthStartdate,

  date(rangemin(dayname(monthend(TempDate)),today())) as MonthEnddate , //Creating the field MonthEndDate

  Date(monthstart(TempDate), 'MMM-YY') AS MonthYear,

  Week(TempDate)&'-'&Year(TempDate) AS WeekYear,

    InWeekToDate(TempDate, $(vToday), 0) * -1 AS CurWTDFlag,

    InWeekToDate(TempDate, $(vToday), -1) * -1 AS PrWTDFlag,

    InMonthToDate(TempDate, $(vToday), 0) * -1 AS CurMTDFlag,

    if (TempDate >= monthstart($(vToday),-1) and TempDate <= monthstart($(vToday),-1)+ $(vToday)-monthstart($(vToday)) ,1,0) as PrMTDFlag,  //Prior Month Flag

  inyeartodate(TempDate, $(vToday), 0) * -1 AS CurYTDFlag,

    if (TempDate >= YearStart($(vToday),-1) and TempDate <= YearStart($(vToday),-1)+ $(vToday)-YearStart($(vToday)) ,1,0) as PrYTDFlag,  //Prior Year Flag

    InQuarterToDate(TempDate, $(vToday), 0) * -1 AS CurQTDFlag,                                                                   

    if (TempDate >= QuarterStart($(vToday),-1) and TempDate <= Quarterstart($(vToday),-1)+ $(vToday)-QuarterStart($(vToday)) ,1,0) as PrQTDFlag  // Prior Quarter Flag

Then YTDSales =

sum({$<CurYTDFlag ={1},Year=,Quarter=,Month=,WeekDesc= >}  [Sales Amount])

PrYTD Sales

sum({$<CurPrYTDFlag ={1},Year=,Quarter=,Month=,WeekDesc= >}  [Sales Amount])

and YTDCompassion =

num(  ( $(YTDSales ) - $(PrYTD Sales ))  /  $(PrYTD Sales ) , '#,##0%')

This way is more efficient than having a complex set analysis by adding these line to the Master Calendar and setting flags.

Hi All,

This document illustrate how to generate the future dates/months  based on different interval for each records.  i.e. for different keys you need to generate different due dates , consider the e.g. below for Key 1 we need the future due date to be generate every 6 months and for Key 2 we need 3 month follow up  ..

Input Data:

*For key 1 we have Month Interval as 6  so we need to dynamically generate future due date months i.e.

OUTPUT for Key 1:

Script:

DATA:
LOAD Key,
[Month Interval],
Amount,
[Due Date],
[Expire Date]
FROM
[C:\Users\avinash5\Desktop\Sample Data.xlsx]
(ooxml, embedded labels, table is Sheet1);

Left Join

LOAD
*,
AddMonths([Due Date] ,([Month Interval]+ ([Month Interval]*IterNo()))) as [Future Due Date]
While
AddMonths([Due Date] ,([Month Interval]*IterNo())) <= [Expire Date]; //Defines when the Interval should end

LOAD Key,
[Month Interval],
Amount,
[Due Date],
[Expire Date]
FROM
[C:\Users\avinash5\Desktop\Sample Data.xlsx]
(ooxml, embedded labels, table is Sheet1);

Note: I have fixed Expire date , you could use different dates also.

Regards,

Avinash R

Hi,

I just wanted to share another example on how to create a Gantt chart in QlikView. To do this we can use some of QlikView's available resources as an alternative to the use of Extension Objects. In this case I have used a Pivot Table.

To create this chart I’ve used some example data (Excel file) that can be found on the zip file attached bellow. That file has several fields, enough to identify the tasks of a Project (such as ID, Start, End…).

In order to be able to pick the desired dates a master calendar has been used. Also, the IntervalMatch functionality has proved very useful by generating the dates between the start and end of the different tasks that the project has.

The Hierarchy functionality in the load script generates the different task levels.

There is something peculiar about the data I am using (Excel file). Colors for each task are specified in Hexadecimal format. So during the load script I converted them to RGB colors (color code used by QlikView).

After the data model has been completed a Pivot Table can be used to represent my Gantt chart. To make the chart stable, on the pivot table properties we need to uncheck the "allow pivoting" option. As dimensions we will have each task level, years months and days.

The expression is a bit different. First of all, I it's represented as an Image. That image is a png file (included in the zip file) with transparency that will let us see the background color of each cell.

The expression is as follows:

if(End=Start,'◆',
      if(date(DateID)=date(Today()),'gantt_part_today.png',
           if(Start = DateID and Group = 1,'gantt_part_end_l.png',
                if(End = DateID and Group = 1,'gantt_part_end_r.png',
                     if(End-DateID+1>0,'gantt_part_mid.png',
                          if(date(DateID)=date(Today()),'gantt_part_today.png'))))))

The background color for each cell is already stored in the data. So we just need to use it as a background color for our expression. In this case, if it is not a milestone, use the color value:

if(Mile =0, RGBColor)

That's all. The Pivot table now can be used as Gantt chart. I hope you find this example useful.

There are many documents and notes which talk about Set Analysis, but until the moment I started to write this manual I didn´t find any in Spanish, so I decided to write it.
Most of it is based on translated adaptations of previous works that I found in the community, to which I have added my own examples that I have been finding over the years. On the last page you will find the references.

This is a first version that will improve with your comments, corrections and contributions.
I hope you find it useful.

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Son muchos los documentos y notas  que hablan de los análisis de conjuntos (Set Analysis), pero hasta el momento de comenzar a redactar este manual no encontré ninguno en Español, así que me decidí a escribirlo.

La mayor parte está basado en adaptaciones traducidas de trabajos previos que encontré en la community, a los que he añadido ejemplos de cosecha propia que me he ido encontrando a lo largo de los años. En la última página encontraréis las referencias.

Esta es una primera versión que iré mejorando con vuestros comentarios, correcciones y aportaciones.

Espero que os sea de utilidad.

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Special thanks to Henric Cronstöm who encouraged me to write it when I was part of the spanish Qlik consulting team.

I am trying to edit my post, however there is not such option available . I tried clicking on the ... on the right hand side.

But there is not such option. The old link used to work very efficiently . This new link is bit pain.

Ejemplos de Función Date# y Date para transformar fechas y luego darles formato. Esto como método alternativo a MakeDate, que usa 4 funciones haciendo más lento el load:

MakeDate(Left(20130628,4), Mid(20130628,5,2),Right(20130628,2))

1. Date#(20130628,'YYYYMMDD')

Devuelve 41453 en formato 20130628

2. Date(Date#(20130628,'YYYYMMDD'),'DD-MMM-YY')

             Devuelve 41453 con formato 28-jun-13

3. Date(Date#('2013.06.28','YYYY.MM.DD'),'DD-MMM-YY')

             Devuelve 41453 con formato 28-jun-13

4. Date(Date#('06-28-13','MM-DD-YY'),'DD-MMM-YY')

             Devuelve 41453 con formato 28-jun-13

5. Date(Date#('28.06.13' ,'DD.MM.YY'),'DD-MMM-YY')

              Devuelve 41453 con formato 28-jun-13

6. Date(Date#('jun.28.2011', 'DD.MMM.YYYY'), 'DD-MMM-YY')

              Devuelve 41453 con formato 28-jun-13

7. Para eliminar hora de una fecha

Date(Floor( '2013-06-28 07:30:38' ),'DD-MMM-YY')

                     Devuelve 41453 con formato 28-jun-13 sin minutos

8. Para obtener solo la hora de una fecha

Time(Frac( '2013-06-28 11:30:38' ),'hh:mm:ss')

     Devuelve 0,47960648148 con formato 11:30:38

9. Para obtener Fecha y Hora

             Date( Timestamp#( '20130628 11:30:38', 'YYYYMMDD hh:mm:ss'), 'DD-MMM-YY hh:mm:ss'   )

                      Devuelve 41453,479606481  con formato 28-jun-13 11:30:38

10. Para obtener día de la semana en fecha

             Date( '2013-06-28', 'WWW DD-MMM-YY')

                      Devuelve 41453  con formato  vie 28-jun-13

11. Resta un mes a la fecha

AddMonths('2013-06-28',-1)

Devuelve 41422 en formato 28/05/2013 (formato predeterminado)

12. Suma un mes a la fecha

AddMonths('2013-06-28', 1)

Devuelve 41453 en formato 28/07/2013 (formato predeterminado)

Esta nota esta publicada en el blog: Qlikview en Español

NOTAS:

Al indicar un formato de hora en Qlikview la letra mayuscula M= mes,  y letra minuscula m= minutos

Referencias (ingles)

http://community.qlik.com/blogs/qlikviewdesignblog/2013/02/19/why-don-t-my-dates-work

http://community.qlik.com/docs/DOC-3102

Hi all,

the product JDBC Connector reached end-of-life because it's technically outdated. Support of it ends 2018-12-31.

The successor product is the TIQ Java Service Connector and can be obtained here:

https://www.tiq-solutions.de/en/tiq-java-service-connector/

Best regards,

Ralf

--------------------------------

Hi everyone,

this is a demo version of our JDBC Connector for QlikView. It will enable a lot of non-ODBC/OLEDB sources for loading data into QlikView.

Also, it would give much more performance on QV 9 loads (Oracle, mysql etc.) and could speed up on complex network environments (like VPN, firewalls, WAN).

Please try it out and give us some feedback!

Installation instructions:

• install the JDBC Connector and config the JDBC driver (mostly a .jar file)
• connect with JDBC URL (e.g. jdbc:oracle:thin:@localhost:1521:ex)
• example connect statement for Oracle (will be included by connect popup):
  CUSTOM CONNECT TO "Provider=JDBCConnector_x64.dll;jdbc:oracle:thin:@localhost:1521:ex;XUserId=SbKdMaFNRLOA;XPassword=aWELJaFNRbTA;";

Missing feature (waiting for QV support):

• no select option for database/owner on table wizzard

Thanks to my colleague Sven "Benno" Uhlig who brought up this idea and implementation!

Ralf

UPDATE:

This is a new version which supports DDL and DML statements too. You will get a result set if a DML statement was executed with the update count.

Just try script calls like this:

DATA:

SQL CREATE TABLE test2 (col1 INT)

;

SQL insert into test2 (col1) values (1)

;

SQL update test2 set col1=2

;

SQL drop table test2

;

UPDATE:

New version comes with an installer and config dialogs. See also: http://www.tiq-solutions.de/display/enghome/ENJDBC for a list of possible JDBC sources.

Have you ever wonder how the examples from the Qlikview help may look like?

Please see below and enjoy responsibly...

Dual()

Definition:

Forced association of an arbitrary string representation s with a given number representation x. In QlikView, when several data items read into one field have different string representations but the same valid number representation, they will all share the first string representation encountered. The dual function is typically used early in the script, before other data is read into the field concerned, in order to create that first string representation, which will be shown in list boxes etc.

Syntax:

where

text - this is first column of data

number- this is second column of data

Qlikview has specific way of storing data. Each field is represented by Text and Number data type called dual.

The Text format is visible to user and the Number is being used for calculation and sorting.

                    You can imagine that each field has top Text layer and bottom

                    (like salad in your Hamburger) Number layer.

This association works most of the time and for most of the data type but there are situation that we need to force this association to work the way we want.

Preparation

Remember

• To use the script below the csv file must be saved in the same folder that your qvd file

Qlik help  example contains two tables: inline and from csv file(sample attached)

load dual ( string,numrep ) as DayOfWeek inline

[ string,numrep

Monday,0

Tuesday,1

Wednesday,2

Thursday,3

Friday,4

Saturday,5

Sunday,6 ];

Directory;

LOAD Date,

    Sales

FROM

afile.csv

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

//The last line  is not part of Qlikview help but you have to have this line to correctly load data into Qlikview

after Reload we have:

As you probably know when Values are on the left hand side they represent Text when on the right Number.

So why we have Text on the right?

In the background (during load) the numbers have been associated  with the text.

So what you see is the bun what you do not is the underlay salad:

Practical use:

Example 1

The simple but maybe not the most useful calculation is to add Days of the week from our data =sum(DayOfWeek)

Example 2

Counting days of the week:

Example 3

(please use attached xlsx file and below Script)

A survey has been conducted and the data in Excel file looks like this:

What we want is to assign more meaningful description to the rate numbers.

Script:

Data:

mapping load * Inline

[ Rate,Value

Very Good,5

Good,4

Average,3

Bad,2

Very Bad,1

]

;

Sort:

load

Responds,

dual(Rate,ApplyMap('Data',Rate)) as Rate

FROM

Survey.xlsx

(ooxml, embedded labels, table is Sheet1);

Now we can create Straight Table or bar Bar Chart:

This function can be use in many different ways.Please research our community to find more examples.

Still feeling hungry?

Do you Qualify?- How to use QUALIFY statement

How to use - Dimensionality()

Missing Manual - GetFieldSelections() + Bonus Example

MaxString &amp; MinString - How to + examples

The second dimension... or how to use secondarydimensionality()

Not authorized to view the specified document 8150

How to use- Dual()