topic Re: QV11 Filtrage d'une table de temps in Francophones

QV11 Filtrage d'une table de temps

Thu, 07 Feb 2013 15:31:27 GMT

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 :

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

rlp — Thu, 07 Feb 2013 15:45:18 GMT

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

Thu, 07 Feb 2013 16:25:27 GMT

Bonjour,

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

Re: QV11 Filtrage d'une table de temps

amauryviseo — Thu, 07 Feb 2013 16:36:18 GMT

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

amauryviseo — Thu, 07 Feb 2013 16:37:03 GMT

Sauf Bastien si on veut éviter des trous dans l'axe temps

Re: QV11 Filtrage d'une table de temps

amauryviseo — Thu, 07 Feb 2013 16:38:00 GMT

Et ce serait plus un LEFT KEEP qu'un LEFT JOIN pour qu'il y ait séparation table de faits, table de dimension date

Re: QV11 Filtrage d'une table de temps

Thu, 07 Feb 2013 16:45:54 GMT

Oui... il faut un Mastercalendar, c'est clair.

Re: QV11 Filtrage d'une table de temps

Fri, 08 Feb 2013 09:04:15 GMT

Re: QV11 Filtrage d'une table de temps

amauryviseo — Fri, 15 Feb 2013 14:24:54 GMT

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

Sat, 16 Feb 2013 16:56:54 GMT

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

Wed, 20 Feb 2013 07:46:20 GMT

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)

Sonia