Filtering / Reducing data using keep prefix

There are plenty features of QlikView that are either underrated or many users don’t know about them at all. The Keep prefix is one of those QlikView hidden treasures.

When filtering tables according to data contained in another table, there’s a well-known function called exists(). However, this post demonstrates that there are some cases when exists() function can lead to unexpected / wrong results.

This is where Keep prefix becomes handy (furthermore,  an alternative solution to a common issue). Let’s take the following requirement as example:

Display the sales amount  per Sales Persons but only for those who have “active” status

The complete sales table includes data for all Sales Persons, including active and inactive ones (in this case the Sales fact table contains historical data). However, there is no field that tells the current status of every Sales Person.

Sales Table

Complete/Original Sales Table

Luckily, this company has a filtered table that includes only active Sales Persons and that will be useful for displaying just the information that is relevant. 

Filter Table

Filter Table

I think the best way of explaining how Keep Prefix works is that it is just like using Join Prefix but without merging different tables.  So it will look for coincidences in Table A and Table B (left, right and inner prefixes can also be used) and return a set or records but without joining them, how awesome is that?

That being said, the solution of this requirement is to use Keep Prefix to filter an original/complete table according to some criteria (in this case, displaying sales related to salespersons only). Besides, the filter table can be stored in a QVD file so it can be reutilized any time this business criteria needs to be applied:

Final Sales Table (sales by salesperson)

Final Sales Table (sales by salesperson)

Sales by SalesPerson

Attached is a qvw showing this QV functionality. In this example, the Original table, the filter table and the final table were loaded unassociated.

It is important to understand, that while QlikView and SQL may seem similar at first, they are very different in terms of logic and structure. Once, this is well understood, advantages of both technologies can be used in favor of creating valuable dashboards to take vital decisions,

Stay tuned for next post

Advertisements

Masters Summit QlikView

 

 

 

Credit:  Bob Thaves, http://frankandernest.com/

Credit: Bob Thaves, http://frankandernest.com/

Masters Summit QlikView was held in Chicago some days ago. This event is getting bigger and bigger with every new edition, mainly because the presenters are exactly what the title of the event says… Masters. It’s impressive how they manage to explain many aspects within QlikView that you may know, but you probably don’t know why they happen or how they work (like explaining how Field = {‘*’} – {‘Value’} is different from Field -= {‘Value’} in Set Analysis).

I really have to recommend this event, because I really think that even the most skilled QlikView developer/designer/consultant/whatever could learn a thing or two from attending this summit.

I can proudly say that since I knew QlikView, I’ve worked together with some of the most brilliant persons within the QlikView ecosystem. I’ve seen them develop some of the greatest QV applications I’ve ever seen. I’ve also heard comments like “I feel like I’ve already learned everything that is possible about QlikView” or “I don’t feel challenged anymore”. However, after 3 days of sessions, even the QlikView experts ended up learning a couple of things or got curious about trying new things.

There is too much material included in the sessions, that you need at least a week to assimilate and comprehend all that knowledge. Besides, learning from the masters is really priceless but not only that, having the chance of talking to them is what makes this event valuable.

Another thing to point out is that getting to know people from other places is a great way to share  and get new ideas about what can be done with QlikView.

In my case, it was the first time I attended Masters Summit, and I think the main purpose for me was to get a wider panorama about what other people is doing and how different is from what I’m doing and to feel that there’s still a long way to go in order to be a QlikView Expert.

 

 

 

Tip: Debugging Set Analysis

Dealing with Set Analysis can be such a pain, especially if you’re not so familiar with syntax or if you’re new to QlikView. However, there’s a simple but useful tip to debug formulas that contain Set Analysis.

Suppose the following requirement:

Escenario

If you’re familiar with Set Analysis, then by now you’ve realized that it can be as easy as this:

Basic Set analysis

but sometimes (ok, maybe most of the times), client’s requirements need something more complex, like using variables and date parameters:

Complex Set Analysis

Fortunately, there’s a way to debug your formulas, instead of “blind coding”:

DebuggingSetAnalysis6In the example used for this post I need:

  • A formula for customers who bought footwear but not clothing during current month
  • A formula for customers who bought footwear but not clothing during past month
  • Find customers who bought footwear in both the past and current month

Simply by creating a straight table with no dimensions and omitting labels for expressions, you can debug your formulas. Just by putting mouse pointer over expression label (which in this case is the expression itself) you’ll see the complete formula but with evaluated variables/functions. In this case I use a variable called vToday in order to store the function Max(OrderDate). So no matter which date is selected, formulas will return customers corresponding to month selected, and for past month. In this example you’ll see that instead of appearing the name of the variable,  it will appear the parameter interval of dates that i’m interested in.

To double check that the formula is correct, I create a straight table with details of quantity bought for current and past month:

Quantity bought current and past month

Looking at the picture, there is only 1 customer that bought footwear but not clothing in both the past and current month.

It’s true that Set Analysis Syntax isn’t the most friendly thing, however tips like these are always welcome.

The complete example can be dowloaded here

Stay tuned for future posts

jaime.aguilar@evolcon.com

Manejo de valores nulos: Null As Value

Retomando el tema de los valores nulos en QlikView, este post está dedicado a una función llamada NULLASVALUE.

Problema: Los valores nulos son muy comunes en prácticamente cualquier fuente de datos. Al ser elementos ausentes o carentes de un valor específico, no pueden ser seleccionados en QlikView, lo que dificulta el poder rastrearlos.

Solución: Como probablemente saben, no es posible seleccionar los valores nulos en QlikView (o al menos no directamente), tal como lo haríamos con cualquier valor de una dimensión. Sin embargo, QlikView ofrece diversas maneras para manejar los valores nulos y facilitar el análisis de información.  Una de las formas es precisamente utilizando la función NULLASVALUE. Esta función permite asignar un valor de texto cuando se encuentre un valor nulo.

  • La manera en que se utiliza es indicando aquellos campos a los que queremos asignar un texto  y definiendo mediante la variable NullValue, cuáll será el texto que sustituirá a los valores nulos.
NULLASVALUE

NULLASVALUE

  • Tomaremos para la explicación de esta función una tabla que contiene un valor de ventas por país y por continente. Para fines de este ejemplo supondremos que no todos los paises tienen relacionado el continente al que pertenecen.

nullasvalue3

  • Como se puede observar en la imagen algunos paises no tienen asociado un continente. De no haber utilizado la función NULLASVALUE, no sería posible seleccionar directamente aquellos valores nulos para el campo Continente.
  • Para hacer más evidente cual es el valor que sustituye a los nulos, se pueden crear cuadros de lista separando los valores de dimensión originales del valor que sustituye a los nulos (en este caso N/A).

nullasvalue2

  • Utilizando las funciones Pick() y Match(), se pueden separar en cuadros de lista distintos, los valores de dimensión, del valor N/A
nullasvalue6

uso de pick() y match()

Resultado: Todos los valores nulos del campo Continente, han sido sustituidos por el texto N/A, haciendo más fácil su rastreo y corrección. En este ejemplo son muy poco datos y es facil detectar en donde están los valores nulos. Sin embargo cuando se trabaja con millones de registros y múltiples fuentes de información, esta función se vuelve especialmente útil para asignar un valor por default para los valores nulos.

Es importante mencionar que esta función elimina los valores nulos, pero no los valores faltantes (missing values). Un ejemplo de los valores faltantes se puede observar si creamos una tabla pivote como la siguiente:

nullasvalue4

Missing values

En este caso hay combinaciones que de paises y continentes que no existen y que son muy evidentes en este ejemplo (como el hecho de que Francia no se encuentra en América). En casos como este la función NULLASVALUE, no será útil. Para manejar los valores faltantes existen otras soluciones como utilizar otro tipo de gráfica que no genere todas las combinaciones posibles.

El ejemplo utilizado en este post, se encuentra disponible en la siguiente liga: https://db.tt/uUxtquBY

Cualquier duda o comentario es bienvenido: jaime.aguilar@evolcon.com

Valores nulos en Mapeos

Esta es mi segunda entrada de blog. En esta ocasión aprovecharé para escribir sobre la función Mapping Load y cómo maneja los valores nulos.

Problema:

Supongamos que tenemos 2 catálogos o dimensiones: Productos y Subcategoría. La dimensión productos incluye nombre de frutas, de bebidas y de algunas especias. Subcategoría sirve para clasificarlos de acuerdo al tipo de alimento al que se refieren. Partiendo de estas 2 dimensiones, supongamos un requerimiento en el cual necesito una agrupación adicional llamada categoría. Esta nueva agrupación o filtro, como su nombre lo indica será más general  que subcategoría, pero además solo incluirá 2 posibles valores: Bebidas (Drink) y comida (Food), descartando los productos que son especias.

Aquí es donde entra la función de mapeo, ya que aprovecharemos el catálogo de subcategoría para “mapear” las categorías que se van a incluir.

Solución:

1. La tabla principal incluye el campo producto y el campo subcategoría. Sobre esta tabla se hará el mapeo del nuevo campo categoría. la tabla se incluye como una tabla Inline

catálogos

2. La tabla de mapeo aprovecha la subcategoría que ya existe.  Primero se incluye como una tabla Inline y posteriormente con la sentencia resident se hace la carga de mapeo (con el prefijo Mapping) y con la sentencia Where descartamos los condimentos, ya que para el campo categoría sólo interesan comida y bebidas

Mapping Load

3.  El comportamiento intuitivo es que si en el punto anterior ya hicimos un resident para descartar los condimentos, al momento de usar la sentencia Applymap, no debería ser necesario utilizar el tercer parámetro que permite asignar un valor por default en caso de que el mapeo no encuentre coincidencias.

applymap

4. Al recargar el QVW vemos que contrario a lo que uno pudiera pensar, los valores de condimento no fueron descartados. Por lo cual el filtro que sólo debería tener los valores Drink y Food, también contiene los valores Herb y Spice, los cuales no nos sirven para este campo. Esto ocurre porque aunque se use la sentencia where para descartar valores, si no se especifica en applymap que hacer con esos valores descartados, QlikView asumirá que también deben ser parte del mapeo.

Cat

5. En este punto es cuando se vuelve importante el tercer parámetro de la función Applymap. En este caso se utiliza para indicar que en caso de no existir coincidencia (los valores Herb y Spice a pesar de existir en el catálogo de subcategoría no deben trasladarse a Categoría), ponga un valor de nulo.

Applymap 2o intento

Resultado:

Si volvemos a recargar el QVW, veremos como efectivamente, los valores descartados no son mapeados

Resultado

Incluyo el archivo para cualquier referencia, cualquier duda pueden escribir a jaime.aguilar@evolcon.com

Archivo qvw: https://www.dropbox.com/s/b23fruwvz8x1wb3/Mapping%20Load.qvw

Tablas de amortización en QlikView

Este es el primer post de mi blog. Quisiera empezar con un tema el cual tuve que representar en QlikView hace algunas semanas.

Las tablas de amortización se pueden manejar fácilmente en Excel por ejemplo. Sin embargo, ¿qué pasaría si quisiera desplegar el desglose de alguna tabla de amortización en QlikView?

Dependiendo como se quieran manejar los datos, las tablas de amortización se pueden precalcular desde el script o bien aplicar las fórmulas directamente en una tabla o gráfica.  Adicionalmente, con el uso de variables se puede generar una calculadora en donde el usuario ingrese datos ficticios para que QlikView calcule las condiciones de pago.

Antes de presentar el funcionamiento en QlikView, recordemos algunos conceptos importantes acerca de las tablas de amortización:

¿Qué es una tabla de amortización?

En términos generales, una tabla de amortización presenta el desglose de una deuda, con los pagos o abonos que se deben hacer para saldar o amortizar dicha deuda. Dependiendo de las condiciones de pago, es posible que las abonos sean variables o fijos, al igual que los intereses.

Términos básicos:

  • Abonos: Para efectos de este ejemplo, los abonos serán fijos y mensuales. Esto significa que cada mes se hace un pago fijo para disminuir la deuda. Cada abono disminuye una parte la deuda original (Capital) y una parte de los intereses generados. Mensualidades = Interes + Capital
  • Capital: Es la cantidad de dinero que se está prestando o financiando. Es la deuda original.
  • Intereses: Es el monto que se genera (En este caso mensualmente) por el préstamo o financiamiento. En otras palabras es el beneficio lque la institución financiera que emite el préstamo o financiamiento obtiene. He aquí el negocio.
  • Saldo Insoluto: Es lo que falta por pagar.
  • Plazo: Es el número de pagos o abonos que se tiene para saldar la deuda.
  • Tasa de interés: Este porcentaje es el responsable del dinero extra que se tiene que pagar en una deuda. Normalmente se representa como tasa de interés anual (Annual Percentage Rate en inglés).

El siguiente video muestra el tutorial para el manejo de tablas de amortización en QlikView:

 

Por último, si tienen algún comentario no duden en escribir al correo: jaime.aguilar@evolcon.com ó si requieren de servicios profesionales visiten la página www.evolcon.com