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