Cálculos al final de cada semana

En esta ocasión nos piden  un gráfico de líneas que nos muestre el stock de un almacén al final de cada semana.

Por cada día, disponemos de un solo registro que acumula todos los movimientos de día, y tiene la siguiente información:

día --> día que se producen los movimientos

Stock_final --> stock al final del día.

Hacer el gráfico por días es muy simple, pero la dificultad viene al hacerlo por semanas. No se puede utilizar una función de agrupación que sume los stock de los días de la semana.

En primer lugar crearemos una variable que almacene la última fecha que tenemos stock. La llamaremos vL.dia_ultimo_stock

En segundo lugar, debemos identificar la semana a la que pertenece cada día. Lo mejor es añadir la siguiente línea en el calendario maestro del script:

 year(date(RecNo()+$(vL.MinDate)-1,'DD/MM/YYYY'))&'-'&week(date(RecNo()+$(vL.MinDate)-1,'DD/MM/YYYY'),0,1) as Año_Semana

o esta otra línea en cada registro de la tabla que contiene el campo día:

 year(date(día,'DD/MM/YYYY'))&'-'&week(date(día ,'DD/MM/YYYY'),0,1) as Año_Semana

Como el gráfico es un evolutivo por semana, no podemos mezclar semanas de distintos años, por eso concatenamos año con el nº de la semana.

Para quedarnos con el stock del último día de la semana, tenemos que identificar cuál es ese día. Aquí es donde vienen las dificultades:

• Si tomamos como último día de la semana el día 6 (suponiendo que el primer día sea el 0), es muy posible que la última semana del año no termine el día 6 y que ese día (normalmente el domingo) esté en la primera semana del año siguiente. Por lo tanto tendremos que identificar también el último día del año. Si no lo identificamos, el gráfico descenderá en esa semana hasta el valor 0. 

En esta imagen y en la siguiente observamos cómo la última semana de 2021 y la última de 2022 no terminan en domingo. como no tenemos datos del domingo, el gráfico desciende hasta el cero

• Y en el extremo contrario, es posible que el día en curso no corresponda tampoco al último día de la semana, por lo que el gráfico también descenderá hasta el valor 0. Por tanto tendremos que identificarlo también.

Este gráfico se calculó un miércoles, mientras que el último día de la semana será el domingo, como todavía o hay datos del domingo, el gráfico desciende hasta el cero

Para evitar todos estos problemas, debemos crear esta expresión de gráfico

SUM(if(día=floor(yearend(día)) OR día=floor(weekend(día)) OR día=$(vL.dia_ultimo_stock), stock_final, 0))

(Observese que tanto yearend() como weekend() devuelven el último segundo del año y semana respectivamnete, por eso truncamos la fecha con floor() )

De esta forma identificamos cuál es el último día de cada año, el último día de cada semana y el día actual y obtenemos el stock final de cada uno de ellos.

Ahora los cálculos para las últimas semanas de cada año y para la semana actual son correctos

Se debe tener en cuenta que de esta forma tendremos semans con menos de 7 día. Por ejemplo, la última semana de 2021 (semana 53) sólo tiene 5 días (de lunes a viernes) y la semana 1 de 2022 sólo tiene 2 días (sábado y domingo)

Visionado condicional del valor de una dimensión

Tenemos datos de apartamentos turísticos y las valoraciones que de ellos hacen los usuarios. Estas valoraciones están agrupadas por categorías. 
Una vez cargados los datos en el modelo, mediante el script añadimos un valor ficticio que contiene las puntuaciones máximas que pueden obtener en cada categoría. Esta puntuación máxima la tratamos como si fuera un apartamento más, o sea, será un valor más de la tabla de apartamentos.

 Lo que queremos es que en un gráfico de tabla pivotante aparezca y desaparezca este valor ficticio a voluntad del usuario. (columna azul de la siguiente tabla) 

 

Obviamente, si tenemos un cuadro de lista con los apartamentos, basta con que el usuario seleccione los que quiera ver y seleccionando el valor “Puntuación Máxima” verá o no la columna azul.

Esta es la solución perfecta cuando el listado de apartamentos tiene pocos elementos, pero como en este caso la lista puede llegar a ser muy larga, no queremos que el usuario tenga que marcar o desmarcar en el cuadro de lista porque por error puede eliminar el resto de selecciones.

Por tanto hemos ideado el siguiente mecanismo:

1º - Creamos una variable que controlará la visualización de los 2 iconos que crearemos a continuación:

LET vL.Punt_Max_SN = 1;

2.-  Creamos un cuadro de texto con esta imagen de fondo y estas propiedades:

 Donde la expresión es:

='("' & Concat(DISTINCT Apartamentos, '"|"') & '"|" Puntuación Máxima")'

 

3.-  Creamos otro cuadro de texto con esta imagen de fondo y estas propiedades:

Donde la expresión es:

='("' & Concat(DISTINCT {<Apartamentos -= {' Puntuación Máxima'}>} Apartamentos, '"|"') & '")'

 

Esta expresión lo que hace es deseleccionar el valor “Puntuación Máxima” del campo Apartamentos.

Y por último, en la pestaña de diseño:

Los iconos aparecerán y desaparecerán de pantalla según el valor de la variable vL.Punt_Max_SN. Ambos estarán ubicados justo en la misma posición para simular el efecto de que efectivamente se trata de un switch.

La acción “seleccionar en campo” permite introducir valores en la caja “Buscar cadena de texto”:

•          Si el campo es numérico, basta con escribir el nº.
•          Si el campo es alfanumérico:  Se puede introducir el valor tal cual o entre comillas simples. Funciona igual de las dos formas y da igual que el valor tenga espacios en blanco. Ej: Aires de Casla.

Cuando se trata de una multiselección, el formato que se debe escribir en la caja “Buscar cadena de texto” debe ser:

•          Si es el campo es numérico: (valor1|valor2|valorn)
•          Si el campo es alfanumérico:  (“valor1”|”valor2”|”valorn”)

Debe observarse que los valores van entre paréntesis y separados por el carácter ‘|’. Además en el caso de valores alfanuméricos, cada uno de ellos se escribe entre comillas dobles ‘ “ ‘ .

 Por tanto, la expresión del switch gris:

 ='("' & Concat(DISTINCT Apartamentos, '"|"') & '"|" Puntuación Máxima")'

 Lo que hace es añadir el valor “ Puntuación Máxima” a las selecciones realizadas en el campo apartamentos, mientras que la expresión del switch verde lo quita:

 ='("' & Concat(DISTINCT {<Apartamentos -= {' Puntuación Máxima'}>} Apartamentos, '"|"') & '")'

 

 

 

 

 

Ordenar valores Hexadecimales

Me he encontrado un proyecto en el que el campo índice es un número en formato hexadecimal y los listados, gráficos, etc deben salir ordenados por ese campo.

 

Imaginemos la siguiente tabla:

LOAD * INLINE [
Campo_Indice
AA

2C

11
20
A3
1A
];

 

Una ordenación por defecto lee los valores y los interpreta como texto o número y los agrupa de forma que primero muestra los texto y después los números.

 

 Observese, en este objeto de lista, cómo los textos aparecen alineados a la izquierda y los números a la derecha.

Si intentamos solucionarlo marcando sólo la opción “TEXTO” en las opciones de ordenación del objeto, la cosa no mejora mucho más:

Una solución rápida consiste en ordenarlo convirtiendo el campo a nº hexadecimal :

 

Pero como se puede observar siguen apareciendo alineados a la izquierda o derecha porque el campo sigue teniendo valores con formato texto y número. No hemos transformado el campo en la carga, sino solo para ordenarlo. De hecho, lo que ocurre durante la ordenación es que transforma los valores del campo a formato numérico decimal y luego los ordena.

Podríamos tener la tentación de utiliar esta fórmula para hacer la transformación directamente en el script de carga. No es aconsejable a menos que este campo no necesite para representarse en ningún objeto de la aplicación, porque el resultado sería el siguiente: 

LOAD Num(Num#([Campo Indice],'(HEX)'))  AS "Campo Indice"  INLINE [ Campo Indice AA 2C 11 20 A3 1A ];

 

Observamos también que si tuviéramos los números de la última lista (pero desordenados) y los quisiéramos convertir a Hexadecimal, bastaría con utilizar esta expresión en el script:  

 

LOAD Num(Num#([Campo Indice]),'(HEX)')  AS "Campo Indice"  INLINE [ Campo Indice 170 44 17 32 163 26 ];

 

Obsérvese cómo en este caso todos aparecen por defecto perfectamente ordenados y alineados a la derecha, lo que indica que realmente son números. En este caso el ‘HEX’ se aplica a la visualización (función num) no a la conversión (función num#).

Por tanto, aplicando todo lo anterior, la mejor forma de hacerlo sería con una doble conversión en el script de carga: de hexadecimal a decimal y nuevamente a hexadecimal.

 

LOAD Num(Num#([Campo Indice],'(HEX)'),'(HEX)')  AS "Campo Indice"  INLINE [ Campo Indice AA 2C 11 20 A3 1A ];

 

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.