Buckets en gráficos

Crear buckets estáticos en el script es fácil. Basta con definir los rangos, darles un nombre y hacer un left join al campo que queremos clasificar, por tanto no profundizaremos más en la explicación. Pero hacer esto mismo desde un gráfico, dinámicamente, en función de los valores que en cada momento tome un campo y dependiendo de las selecciones realizadas en cada momento, es un poco más complicado.

Se trata de evitar crear expresiones en los gráficos de este tipo:

IF($(vL.Puntuacion)=0, 'No disponible',
  IF($(vL.Puntuacion) < 50 , 'Disponible con deficiencias graves',
    IF($(vL.Puntuacion) < 80 , 'Disponible con deficiencias leves',
      IF($(vL.Puntuacion) < 101 , 'Disponible sin deficiencias'))))

 

Donde vL.Puntuacion es una variable que calcula un % y cuya expresión es:

(SUM(Valoracion_Real) / SUM(Punt_Max_Elemento)) * 100

                              

                A su vez Valoracion_Real es un campo del modelo de datos

Y Punt_Max_Elemento es un campo precalculado en el script con la suma de Valoracion_Real, aunque también podríamos usar SUM({1} Valoracion_Real)  o SUM( TOTAL  Valoracion_Real)

Estas expresiones representan 2 problemas: Por un lado los IF son pesados de procesar, y por otro generan bastante trabajo de mantenimiento porque obligan a buscar y corregir las expresiones gráfico a gráfico cada vez que se añadan o modifiquen los intervalos, con el consiguiente riesgo de que nos dejemos alguna

Primero preparamos la tabla en un Excel con los buckets:

Valoración	Descripción	Intervalo %	Inter_Desde	Inter_Hasta	Color
0	No disponible	0%	0	0	RGB(215,219,221)
1	Disponible con deficiencias graves	1%-49%	1	49	RGB(241,148,138)
2	Disponible con deficiencias leves	50%-79%	50	79	RGB(248,196,113)
3	Disponible sin deficiencias	80%-100%	80	100	RGB(130,224,170)

segundo preparamos el script para leer esa tabla y generar una tabla con todos los posibles valores de los intervalos;

//*********************************************************************************************************
//                       DESCRIPCION VALORACIONES DE ELEMENTOS
//*********************************************************************************************************

DESC_VAL_ELEMENTOS_TMP:
LOAD Valoración,
     Descripción,
     [Intervalo %],
     Inter_Desde,
     Inter_Hasta,
     Margen_sup,
     Color
FROM [import\Plantilla Servicios.xlsx]
(ooxml, embedded labels, table is [Valoracion Elementos]);

For i=1 to NoOfRows('DESC_VAL_ELEMENTOS_TMP')

    let vlimite_desde=Peek('Inter_Desde',-$(i),'DESC_VAL_ELEMENTOS_TMP'); //aquí toma el margen superior de cada intervalo.
    let vlimite_hasta=Peek('Inter_Hasta',-$(i),'DESC_VAL_ELEMENTOS_TMP'); //aquí toma el margen superior de cada intervalo.
   
    For j=$(vlimite_desde) To $(vlimite_hasta)
   
        DESC_VAL_ELEMENTOS:
        LOAD
          Peek('Valoración',-$(i),'DESC_VAL_ELEMENTOS_TMP')     AS Valoración,
          Peek('Descripción',-$(i),'DESC_VAL_ELEMENTOS_TMP')   AS Descripción,
          Peek('Intervalo %',-$(i),'DESC_VAL_ELEMENTOS_TMP')    AS [Intervalo %],
          Peek('Color',-$(i),'DESC_VAL_ELEMENTOS_TMP')             AS Color,
          $(j)                                                                                           AS Valoracion_Elemento
        Autogenerate 1;
   
    Next j
   
Next i


DROP TABLE DESC_VAL_ELEMENTOS_TMP;

Esto genera la tabla:

 

Después la expresión del gráfico es:

=Pick(Floor(SUM(Valoracion_Real)/SUM(Punt_Max_Elemento)*100)+1,$(=Concat(''''&Descripción&'''',', ',Valoracion_Elemento)))

 Obsérvese las 4 comillas simples que rodean a “descripción” generan una única comilla. También se pueden sustituir las 4 comillas simples por CHR(39)

Y para el color de fondo, editamos la propiedad de color de fondo de la expresión y ponemos esta fórmula:

=Pick(Floor(SUM(Valoracion_Real)/SUM(Punt_Max_Elemento)*100)+1,$(=Concat(Color,', ',Valoracion_Elemento))) 

 Observese que se han suprimido las 4 comillas.

 Curiosamente, si lo que queremos es colorear el fondo de un objeto de texto, podremos utilizar la misma fórmula usada para colorear el fondo de la expresión, o esta otra que no funciona en los gráficos:

=$(=Pick(Floor(SUM(Valoracion_Real)/SUM(Punt_Max_Elemento)*100)+1+1,$(=Concat (''''&Color&'''',', ',Valoracion_Elemento))))

 Observese aquí que la expresión comienza por $(=…. Y se siguen manteniendo las 4 comillas.

 

Formateo de texto

Para aquellos que como yo somos más bien olvidadizos con ciertas sintaxis, conviene tener a mano esta entrada para formatear textos en las expresiones y dimensiones de los gráficos.

Propiedades de gráfico -> Pestaña de dimensiones o expresiones -> pinchar en el símbolo ‘+’ a la izquierda de la dimensión o expresión -> En formato de texto escribir:

                =‘<B>’      à Negrita (Bold)

                =’<I>’        à Cursiva (Italic)

                =’<U>’      à Subrayado (Underscore)

 Se pueden usar en expresiones condicionales, por ejemplo:

                    =IF(LEN(TRIM(ID_LISTADO))=9,'<I>','<B>')

 Donde ID_LISTADO es un campo de una tabla del modelo de datos que en este caso contiene valores con longitudes 5, 7 ó 9

 Y también se pueden combinar. Estas 2 opciones son válidas:

·                                 = ‘<B>’&’<U>’

·                                 = ‘<B><U>’

 

Ejemplo:

=IF(LEN(TRIM(ID_LISTADO))=9,'<I>','<B><U>')

► Carga de ficheros no estructurados

Vamos a analizar paso por paso cómo cargar un fichero no estructurado.
Este tipo de ficheros suelen ser informes generados por otras herramientas y exportados a ficheros de texto. Suelen tener cabeceras, pies de página, líneas de texto e información relevante que no siempre está encolumnada en las mismas prosiciones.
Ejemplos: facturas, informes financieros, listados.... etc.

El truco para poderlo cargar es buscar palabras claves que se repitan y que nos den un indicio de dónde empieza y termina la información relevante.

En este caso vamos a cargar el fichero WEB http://www.linux-usb.org/usb.ids.
Este fichero, entre otras cosas contiene una lista de nombres de fabricantes y de dispositivos USB. Está más o menos formateado ya que las líneas comienzan siempre igual: por un carácter en blanco, tabuladores o '#' para los comentarios.

Contiene varias secciones, pero nos vamos a quedar sólo con la parte de los vendedores y sus dispositivos.
La estrategia a seguir es similar en todos estos ficheros y comienza cargando todo el fichero en una tabla. Luego buscaremos registro a registro la información relevante y la almacenaremos en una tabla de hechos.
En este caso cada línea del fichero puede tener un vendedor, un dispositivo o un interface. O sea, sólo uno a la vez. en cambio nuestra tabla de hechos tendrá un registro por cada combinación de vendedor, sus dispositivos y los interface de cada dispositivo.

 Id_Vendedor  |  Vendedor  |  Id_Dispositivo  |  Dispositivo  |  Id_Interface  |  Interface 

Veamos los pasos detalladamente.

1.- El modo de lectura por defecto de Qlik quita los espacios en blanco y los tabuladores a la izquierda del primer carácter de cada campo leído. Nos interesa leer la línea del fichero sin que se pierda nada, por tanto ponemos:

       SET VERBATIM = 1;

Esto nos va a garantizar cargar las líneas bien tabuladas.

2.- Cargamos cada línea del fichero en un registro de una tabla temporal. Luego borraremos esta tabla.

       DISPOSITIVOS_TMP:
        LOAD recno() AS "Num Linea",
                  @1        AS Linea
        FROM [http://www.linux-usb.org/usb.ids] (txt, utf8, no labels, delimiter is ';');

3.- Volvemos a establecer el modo de carga por defecto

      SET VERBATIM = 0;

4.- Ya tenemos cargada cada línea del fichero original en el campo "Línea" de la tabla "DISPOSITIVOS_TMP". Vamos a recorrer cada registro de la tabla para buscar y obtener la información relevante. El registro leído lo metemos en una variable para poderlo manipular.

       FOR i = 0 to NoOfRows('DISPOSITIVOS_TMP')-1
               vL.Linea=peek('Linea',$(i),'DISPOSITIVOS_TMP');

5.- Sabemos que el bloque de vendedores comienza por una línea que contiene el texto '# vendor  vendor_name' y que el siguiente bloque de datos comienza en una línea que contiene el texto '# C class  class_name'. Por tanto estableceremos un FLAG que indique si estamos en el bloque de vendedores o no.

       IF substringcount(vL.Linea,'# vendor  vendor_name') > 0 THEN // Comienzo Lista de vendedores
              vL.Flag_Fabricantes=1;
       END IF
       IF substringcount(vL.Linea,'# C class  class_name') > 0 THEN // Fin Lista de vendedores
              vL.Flag_Fabricantes=0;
       END IF

6.-  Llega el momento de verificar la información de cada línea. Si la línea pertenece al bloque de los Vendedores, la analizaremos, si no, directamente la desechamos.

       IF vL.Flag_Fabricantes=1 THEN  

7.- Para analizarla sabemos:

•  que las líneas de comentarios comienzan por '#'
•  que las líneas que comienzan por ' ' (espacio blanco) son líneas en blanco a desechar
•  que las líneas de los dispositivos comienzan por un caracter tabulador (CHR(9))
•  que las líneas de los interfaces comienzan por 2 caracteres tabulador (CHR(9)&CHR(9))

Dependiendo del tipo de línea detectada, guardaremos su información relevante en variables diferentes. 

     IF MATCH(LEFT(vL.Linea,1),'#',CHR(09),' ') = 0 THEN //Si no hay tabs ni #, es una línea de fabricante
              vL.Id_Fabricante = SubField(vL.Linea,' ',1); 
              vL.Nombre_Fabricante = TRIM(MID(vL.Linea, index(vL.Linea,' ')));
          
              //Cada vez que cambiamos de fabricante reseteamos los valores de los dispositivos y de los interfaces
              vL.Id_Interface='';
              vL.Nombre_Interface='';
              vL.Id_Dispositivo='';
              vL.Nombre_Dispositivo='';
          
              vL.Grabar_Linea=1;
       
             ELSEIF LEFT(vL.Linea,2) = CHR(09)&CHR(09) THEN // Es un Interface
                  vL.Id_Interface = Subfield(REPLACE(vL.Linea,'\t',''),' ',1);  
                  vL.Nombre_Interface = TRIM(MID(vL.Linea, index(vL.Linea,' ')));
             
                  vL.Grabar_Linea=1;
          
                 ELSEIF LEFT(vL.Linea,1) = CHR(09) THEN // Es un Dispositivo
                       vL.Id_Dispositivo = Subfield(REPLACE(vL.Linea,'\t',''),' ',1);  
                       vL.Nombre_Dispositivo = TRIM(MID(vL.Linea, index(vL.Linea,' ')));
                
                       //Cada vez que cambiamos de dispositivo reseteamos los valores de los interfaces
                       vL.Id_Interface='';
                       vL.Nombre_Interface='';
                
                       vL.Grabar_Linea=1;                
       ENDIF    

      

8.-  Si en cualquier IF anterior se ha marcado la línea como "grabable", procedemos a meterla en la tabla de hechos

      IF vL.Grabar_Linea=1; //Con este IF no duplicamos la última línea válida antes de una inválida que comience por #

           DISPOSITIVOS:

            LOAD

                 '$(vL.Id_Fabricante)'             AS Id_Vendor,

                 '$(vL.Nombre_Fabricante)'   AS Vendor,

                 '$(vL.Id_Dispositivo)'            AS Id_Device,

                 '$(vL.Nombre_Dispositivo)'  AS Device,

                 '$(vL.Id_Interface)'               AS Id_Interface,

                 '$(vL.Nombre_Interface)'     AS Interface              

             AUTOGENERATE 1;

    

          vL.Grabar_Linea=0;   //Reseteo del Flag que nos indica que hay que grabar la línea

       ENDIF  

9.- Repetimos el proceso para una nueva línea

      ENDIF //Cierre del IF que preguntaba si estamos en el bloque de Vendedores
    
     NEXT i  //Fin del bucle que lee línea a línea de la tabla temporal

10.- Por último borramos la tabla auxiliar

         DROP TABLE DISPOSITIVOS_TMP;


Con esto ya tendríamos transformado el fichero en una tabla lista para ser usada en nuestro Dashboard.

La filosofía para cualquier fichero de texto es siempre la misma: Cargar todo el fichero, fila a fila, en un campo de una tabla y luego leer cada registros de la tabla buscando información relevante como palabras clave que se repitan o patrones de palabras. Para ello usaremos con asiduidad casi todas las funciones de cadena que permitan trocear y limpiar texto como SUBFIELD, TRIM, LEFT, MID, RIGHT, INDEX, SUBSTRINGCOUNT...
Tendremos que crear variables de control para cargar la línea en curso y saber qué debemos hacer con ella. Usaremos también bucles FOR-NEXT para repetir trabajo sobre líneas, controles IF-THEN-ELSE para verificar si tenemos que tratarla o desecharla y por último un bloque donde grabaremos registro a registro los datos extraídos previamente en variables.

► Fichero con el listado de tablas del modelo

Esta entrada es una variación de la que hablaba de cómo exportar todas las tablas de un modelo.
Me encuentro ahora en la siguiente situación: Tengo un cuadro de mandos que hace un BINARY de otro, pero sólo necesito algunas tablas del CdM original, por lo que tengo que borrar las que me sobran.
Por alguna razón otro desarrollador ha modificado el CdM original y han desaparecido tablas, por lo que ahora mi cuadro de mandos me falla con el siguiente error:

En condiciones normales bastaría con mirar en el visor de tablas las tablas que tenemos e ir comparando con las que se borran en el script. Pero resulta que nuestro modelo tiene muuuuchas tablas que no caben en pantalla a no ser que se reduzca la resolución del visor de tablas, y en ese caso los nombres de las tablas dejan de ser visibles.

Por tanto necesitamos algún sistema que nos saque un listado de las tablas del modelo.

Existen 2 soluciones:

1º la más fácil:
Antes del punto que provoca el error interrumpimos el script con un exit script:
En cualquier pestaña de visualización creamos un cuadro de lista donde el campo elegido es el campo del sistema $Table.
Una vez que tengamos la lista la podemos exportar a excel.

2º Desde el script:
Antes del punto que provoca el error escribimos el siguiente código que recorre las tablas del sistema, mete sus nombres en una tabla temporal, exporta la tabla y luego la borra:

LET numTablas = NoOfTables();

FOR i = 0 to $(numTablas)-1
       LET Tabla= TRIM(TableName($(i)));
          
       Mis_Tablas:
       LOAD '[$(Tabla)]' as NombreTabla
       AUTOGENERATE 1;
      
      Store Mis_Tablas INTO Mis_Tablas.txt (txt);
NEXT i

Drop table Mis_Tablas;

exit script;

► Análisis rápido de campos

A menudo, durante la fase de desarrollo de un cuadro de mando,  usamos los cuadros de lista con el único fin de ver qué valores han cargado.
Eso nos obliga a crear tantos cuadros de lista como campos queremos comprobar.

Existe otra forma más fácil y rápida para analizar TODOS los campos de la aplicación con solamente 2 objetos.

Veamos el proceso.

1.- Crear un cuadro de lista para el campo $Field. Este es un campo del sistema que contiene los nombres de todos los campos de la aplicación. Si, cuando creamos el cuadro de lista, $Field no aparece entre los posibles valores de campos, elegimos la opción "Expresión" y escribimos directamente $Field

2.- Crear un gráfico de tabla simple de la siguiente forma (es importante copiarlo tal como aparece escrito a continuación:

                         Dimension calculada: =$(='[' & only([$Field]) & ']')

                         Expresión:    sum({1}1)

Ahora, cada vez que elijamos un campo del cuadro de lista, la tabla simple nos dará todos los valores del campo y su frecuencia (las veces que se repiten)

► FIRST vs SAMPLE

¿Qué pasa cuando tenemos que probar un cambio mínimo en el script pero la recarga dura horas porque carga millones de registros?
Una situación así puede acabar con la paciencia de cualquiera. Cada cambio, por mínimo que sea nos obligará a esperar.... esperar... esperar....
¿Cómo evitarlo?
La respuesta es obvia. No cargando todas las filas del modelo. Sólo unas pocas. Las suficientes para probar si el script falla y que los objetos se visualicen correctamente.

Para cargar sólo unas cuantas filas existen 2 estrategias: usando FIRST y SAMPLE.

FIRST n carga las n primeras filas de la tabla
SAMPLE n (donde n está entre 0 y 1) carga n %  filas aleatorias., ej: 0,5 carga el 50% (una de cada 2). 0.0001 carga 1 de cada 10.000... etc)

Independientemente de si usamos una estrategia u otra, conviene siempre crear una variable que guarde el nº de filas a cargar. De esta forma nos evitamos comentar todas las sentencias FIRST y SAMPLE en el script cuando no las queramos usar, pudiéndolas dejar descomentadas.

LET vNumFilas= 1000000000; //Un número más grande que el nº máx de registros) en el caso de usar FIRST

LET vNumFilas= 1; // En caso de SAMPLE esto cargará todas las filas.

Basta con modificar sólo el valor de la variable para obtener el nº de filas deseadas en el modelo.

Estrategia con FIRST.

El inconveniente es que no toma un número de registros homogéneo, si no sólo los n primeros registros.
Imaginemos una tabla con 10 millones de facturas. Con toda probabilidad estará ordenada por fecha. Si tomamos sólo las 1.000 primeras facturas es casi seguro que todas corresponderán al mismo mes, por lo que cualquier objeto calendario no se visualizará correctamente porque sólo mostrará ese mes y ese año.

Estrategia con SAMPLE 

Nos permite obtener 1.000 registros de fechas variadas de la misma tabla de 10 Millones de facturas. basta con usar SAMPLE 0.0001 para quedarnos con una de cada 10.000.

ejemplo con SAMPLE.
Vamos a suponer que tenemos un modelo con varias tablas. tenemos una tabla de hechos con 15 millones de registros. Queremos cargar sólo 1 de cada 10.000. Del resto de tablas sólo nos interesan los registros que estén relacionados con la tabla de echos, por lo que usaremos un WHERE EXISTS en cada una de ellas.

Para no tener que comentar y descomentar los WHERE EXISTS y el SAMPLE cada vez que lo queramos usar, lo mejor es crear variables.

LET vNumFilasMuestra= 0.0001;

SAMPLE ($(vNumFilasMuestra))
Tabla_A:
LOAD %KeyB,  //Campo que une con la tabla B
     %KeyC,  //Campo que une con la tabla C
     campo1, campo2, campo3..... campoN
FROM A; //Sustituir A por una tabla, fichero qvd, excel... etc.

// establecemos una condición para cargar sólo las filas de la tabla B cargadas previamente en en la tabla_A
LET vCondicionWHERE = IF($(vNumFilasMuestra)<1,'WHERE EXISTS(%KeyB)';

Tabla_B:
LOAD %KeyB,
     campo_b1, campo_b2, campo_b3..... campo_bN
FROM B //Sustituir B por una tabla, fichero qvd, excel... etc.
$(vCondicionWHERE)
; //La sentencia termina aquí, no después del FROM.

// establecemos una condición para cargar sólo las filas de la tabla C cargadas previamente en en la tabla_A
LET vCondicionWHERE = IF($(vNumFilasMuestra)<1,'WHERE EXISTS(%KeyC, IF(Zona=3, '& CHR(39)& 'Madrid' & CHR(39) & ', Zona)& ' & CHR(39) & '-' & CHR(39) & ' & Provincia)');

Tabla_C:
LOAD IF(Zona=3, 'Madrid', Zona)&'-'& Provincia AS %KeyC,
     campo_c1, campo_c2, campo_c3..... campo_cN
FROM C //Sustituir C por una tabla, fichero qvd, excel... etc.
$(vCondicionWHERE)
; //La sentencia termina aquí, no después del FROM.

Observad que el campo clave %KeyC en la tabla C es un campo compuesto por otros 2, Zona y Provincia. Pero además se usa una condición para formar el campo.
La correspondiente condición WHERE sería:

WHERE EXISTS (KeyC, IF(Zona=3, 'Madrid', Zona) & '-' & Provincia)

Pero eso no se puede asignar así, tal cual, en una variable porque las comillas simples se interpretarían como el final de la cadena de texto y provocaría un error.

La forma de conseguirlo es sutituir en la variable vCondicionWHERE todas las comillas simples por su correspondiente código ASCII y tener mucho cuidado de dividir toda la cadena WHERE en partes más pequeñas que se van concatenando con '&'. 

► Exportar todas las tablas del modelo

En ocasiones tenemos la necesidad de exportar todas las tablas del modelo. Por supuesto este proceso se puede hacer tabla a tabla con su consiguiente sentencia STORE justo después de cargar la tabla en memoria.
La forma más eficaz y elegante de hacerlo es con un bucle. De esta forma no importa que en un futuro añadamos más tablas en el modelo. No tendremos que acordarnos de escribir su correspondiente STORE ya que el bucle lo hará automáticamente.

Para ello nos valdremos de las funciones NoOfTables() y TableName() y de cómo nombra realmente Qlik las tablas internamente, que es mediante un índice. A la primera tabla la asigna el índice 0.
La función NoOfTables() devuelve el Nº de tablas que componen el modelo.
La función TableName(<índice>) devuelve el nombre de la tabla correspondiente al índice.


LET numTablas = NoOfTables();


FOR i = 0 to $(numTablas)-1
       LET Tabla= TRIM(TableName($(i)));
          
       STORE [$(Tabla)] INTO [$(Tabla)].csv (txt);
      
      //drop table [$(Tabla)]; //Si queremos borrar la tabla del modelo   
NEXT i

En el ejemplo la exportación se hace a ficheros .csv (realmente son ficheros de texto) pero también se podrían exportar a .qvd (qvd).

En la siguiente variante añadimos una traza, un path de exportación, discriminamos todas las tablas temporales y a todos los los .qvds generados les forzamos que los nombres comiencen por 'ERP_'.

SET QVD_Path_Extraccion = 'lib://Qlik_Deployment_Framework (user_xxx)/2.QVD/1-Capa_Extraccion/';

LET numTablas = NoOfTables();

FOR i = 0 to $(numTablas)-1

      LET Tabla= TRIM(TableName($(i)));

      

      IF index('$(Tabla)','TMP')=0 THEN

          TRACE 'Guardando   ----> $(Tabla)';

          STORE [$(Tabla)] INTO '$(QVD_Path_Extraccion)ERP_$(Tabla).qvd' (qvd);

      END IF  

      

      //drop table [$(Tabla)]; //Si queremos borrar la tabla del modelo   

NEXT i