10 Replies Latest reply: Feb 20, 2013 2:46 AM by soniagent RSS

QV11 Filtrage d'une table de temps

Amandine LEFEVRE

Bonjour,

 

J'utilise une table de temps constuite à partir d'un QVD temps lui-même construit à partir d'une table temps Oracle. Voici un extrait du code permettant de charger le QVD :

code qvd temps.jpg

La table temps est reliée à ma table de fait par le champ DATE.

La table temps n'a pas été filtrée en amont et contient toutes les lignes de la table Oracle (toutes les dates depuis environ 1980).

Je souhaiterais filtrer ma table de temps pour n'avoir que les dates qui rentrent dans le "périmètre de temps de ma table de fait".

C'est-à-dire : DATE >= min(table de fait) et DATE <= max(table de fait).

Mais je ne vois pas comment le faire dans le script...

Cela n'est surement pas compliqué mais je ne trouve pas un moyen simple et rapide de le faire.

D'avance merci pour votre aide,

Amandine Lefèvre

  • Re: QV11 Filtrage d'une table de temps
    Richard Pressanti

    En supposant que votre table de faits s'appelle fact_table , il suffit de récupérer le min et le max par:

     

    Extremum:

    NoConcatenate

    LOAD

         min(DATE) as date_min ,

         max(DATE) as date_max

    RESIDENT fact_table ;

     

    LET date_min = peek( 'date_min' , 0 , 'Extremum') ;

    LET date_max = peek('date_max' , 0 , 'Extremum') ;

     

    DROP TABLE Extremum ;

     

     

    d'ou ensuite le chargement proprement dit:

     

    TD_PTNTPS:

    NoConcatenate

    LOAD

         ...

    FROM ...

    WHERE

         DATE >= '$(date_min)'

    AND

         DATE <= '$(date_max)'

    ;

    • Re: QV11 Filtrage d'une table de temps
      Amaury Moreau

      Bonjour,

       

      Solution 2

      ========

      plus optimum mais effectivement moins clair à la lecture

       

      (Préalablement charger la table de faits qui contient le champ DATE)

       

      TEMP_DATE:

      LOAD fieldvalue('DATE',iterno()) as DATE

      AUTOGENERATE 1

      WHILE len(fieldvalue('DATE',iterno()));

       

      (Autre possibilité mettre dans une variable nbDates le FieldValueCount('DATE'), faire une boucle for i de 1 à nbDates avec FieldValue('DATE', $(i))...Plus long à l'ecriture mais plus parlant que la fonction iterno())

       

      Extremum:

      NoConcatenate

      LOAD

           min(DATE) as date_min ,

           max(DATE) as date_max

      RESIDENT TEMP_DATE ;

       

       

      LET date_min = peek( 'date_min' , 0 , 'Extremum') ;

      LET date_max = peek('date_max' , 0 , 'Extremum') ;

       

      drop table Extremum;

      drop table TEMP_DATE;

       

      (....)


      En effet si la table de faits est très volumineuse le min et le max va nécessiter à QlikView de la CPU et de la mémoire temporaire (pour parcourir les pointeurs de la table de faits)

       

      Alors que la fonction fieldvalue va permettre de parcourir la table des symboles du champ DATE !

       

      De quelques secondes on passe à une instruction qui se fait en centième de seconde et sans aucune consommation mémoire supplémentaire

       

      Cette technique en plus est très utile lorsqu'on souhaite également construire des clefs numériques dans sa table de faits et à la volée une table de référence avec les labels .. Si cette table de référence CLE NUM, LABEL ne fait pas partie de ses données source (ce qui arrive souvent lorsqu'on se connecte à un système de gestion, et non à un DWH)

       

      Bien sûr cette technique est à utiliser une fois qu'on maitrise bien les scripts la solution de Richard reste plus simple dans un premier temps mais je profite de la question d'Amandine pour l'aborder car peu de développeurs QlikView connaisse/utilise cette technique

       

      Solution 3

      ========

       

      (Préalablement charger la table de faits qui contient le champ DATE)

       

      TD_PTNTPS_TEMP:

      NoConcatenate

      LOAD

           ...

      FROM ...

      where exists(DATE);

       

      Le where exists() sans utilisation du deuxième paramètre a la chance de nous laisser en QVD Optimized

      Et de restreindre la table des dates à celles préalablement observée dans la table des faits

       

      Le problème est qu'il peut y avoir des trous dans l'axe temps....Donc on va faire un MIN/MAX et appliquer la même méthodo que proposé par Richard

       

      Extremum:

      NoConcatenate

      LOAD

           min(DATE) as date_min ,

           max(DATE) as date_max

      RESIDENT TD_PTNTPS_TEMP;

       

      On fait donc un min/max sur une table beaucoup plus petite que la table de faits

       

      LET date_min = peek( 'date_min' , 0 , 'Extremum') ;

      LET date_max = peek('date_max' , 0 , 'Extremum') ;

       

      DROP TABLE Extremum ;

       

      TD_PTNTPS:

      NoConcatenate

      LOAD

           ...

      FROM ...

      WHERE

           DATE >= '$(date_min)'

      AND

           DATE <= '$(date_max)'

      ;

       

       

      On charge deux fois la table des temps mais vu sa taille ce n'est pas bien méchant

       

      Bref sous QlikView toujours plusieurs possibilités

       

      Cdt

        • Re: QV11 Filtrage d'une table de temps
          Amaury Moreau

          Bonjour Amandine,

           

          Le ORDER BY ne fonctionne pas lors du chargement d'un QVD

          Et combien même cela serait possible cela fera perdre le mode "QVD Optimized" au chargement du QVDs...Le temps de chargement devient gigantesque avec 1 milliard de lignes

           

          Le ORDER BY ne peut s'effectuer que sur une table en mémoire avec un RESIDENT et nécessite donc de la précharger une première fois

           

          L'intérêt de passer par un fieldvalue (et l'iterno() est utilisé pour boucler autour de cette fonction) est que les différentes fonctions de max() ne vont pas être faites sur une grosse table de faits mais sur une table qui ne va contenir que les valeurs distinctes de date

           

          fieldvalue va permettre de requêter dans la table des symboles du modèle QlikView, ou plus exactement de charger dans une table chaque valeur distincte de date dans la table de faits ... Ce qui est extrement rapide et peu consommateur en mémoire

           

          Voir par exemple ce lien qui explique le fonctionnement global du modèle QlikView : http://community.qlik.com/blogs/qlikviewdesignblog/2012/11/20/symbol-tables-and-bit-stuffed-pointers

           

          Va voir du côté de l'API "QlikView Components" et de la fonction Qvc.GetFieldValues

          Qui utilise maintenant cette méthode pour récuperer le min/max d'un champ

          http://code.google.com/p/qlikview-components/

           

          Rob propose une syntaxe encore plus simple avec le FieldValue

           

          LOAD

          FieldValue('$(_field)', recno()) as [$(_field)]

          AUTOGENERATE FieldValueCount('$(_field)')";

           

          qui evite de passer par une boucle for ou un iterno()

           

          FieldValueCount renvoie le nombre de valeurs distinctes d'un champ...Ce n'est pas lié à une table mais à toutes les occurences possibles d'un champ dans le datamodel (Cas des champs clefs qui se retrouvent dans plusieurs tables)

      • Re: QV11 Filtrage d'une table de temps
        soniagent

        bonjour,

         

        Cette technique en plus est très utile lorsqu'on souhaite également construire des clefs numériques dans sa table de faits et à la volée une table de référence avec les labels .. Si cette table de référence CLE NUM, LABEL ne fait pas partie de ses données source (ce qui arrive souvent lorsqu'on se connecte à un système de gestion, et non à un DWH)

         

        je me permets de rebondir sur cette phrase. Je comprends bien l'intérêt de la fonction FieldValue pour créer une table de référence avec des valeurs distinctes, mais comment facilement, extraire ces données d'une table de faits et de faire un lien sur une clé numérique ? Faudra-il d'abord créer la table avec un RrowNo par exemple, puis faire un ApplyMap sur la table de Faits ? serait-ce performant ?

         

        Sonia

  • Re: QV11 Filtrage d'une table de temps
    Bastien Rogowski

    Bonjour,

     

    Un simple LEFT JOIN devrait faire l'affaire, non?

  • Re: QV11 Filtrage d'une table de temps
    soniagent

    bonjour Amaury,

     

    je me permets de revenir sur l'idée que vous lanciez qui semble effectivement intéressante.

     

     

    Cette technique en plus est très utile lorsqu'on souhaite également construire des clefs numériques dans sa table de faits et à la volée une table de référence avec les labels .. Si cette table de référence CLE NUM, LABEL ne fait pas partie de ses données source (ce qui arrive souvent lorsqu'on se connecte à un système de gestion, et non à un DWH)

     

    je me permets de rebondir sur cette phrase. Je comprends bien l'intérêt de la fonction FieldValue pour créer une table de référence avec des valeurs distinctes, mais comment facilement, extraire ces données d'une table de faits et de faire un lien sur une clé numérique ? Faudra-il d'abord créer la table avec un RrowNo par exemple, puis faire un ApplyMap sur la table de Faits ? serait-ce performant ?

     

    Sonia