16 Replies Latest reply: Jul 24, 2013 11:05 AM by Matr Thieu RSS

    Modélisation en étoiles avec 2 tables de fait

      Bonjour,

      Voici ma structure :

       

      1 table "dimension" CLIENT :

      "N° client"

      "Secteur"

      "Nom"

      "prénom"

      ....

       

      1 table "dimension" PRODUIT :

      "Ref"

      "Désignation"

      "groupe produit"

      ....

       

      1 table "dimension" CALENDRIER

      "date"

      "mois"

      "année"

      ....

       

      puis,

       

      1 table "de fait" FACTURE :

      "N° client"

      "date"

      "Ref"

      "Qte facture"

      "Montant facture"

      .....

       

      1 table "de fait" AVOIR :

      "N° client"

      "date"

      "Ref"

      "Qte facture"

      "Montant facture"

      ......

       

       

      Mon objectif : c'est de calculer le CA net 'facture - avoir' en fonction d'une classification Client et/ou Produits et en fonction du temps.

       

      Problème : En faisant cette structure qlikview me génère un cycle donc ça ne va pas

       

      Merci d'avance pour vos remarques et analyses

       

      Mathieu

        • Re: Modélisation en étoiles avec 2 tables de fait
          Richard Pressanti

          Il vous faut créer une table de liens (en anglais Link Table) centrale qui assurera la connexion avec vos deux tables de faits et vos tables de dimensions.

          Concrétement, votre table de liens contiendra vos 3 champs problématiques qui devront donc être supprimés des tables de fait. Tout le problème est de récupérer toutes les combianaisins distinctes de vos deux tables de liens.

          Le code suivant devrait fonctionner.

           

          // Elimination des doublons propres à FACTURE

          tmp_Link_Table:

          NoConcatenate

          LOAD DISTINCT

               [N° client] ,

               date ,

               ref

          RESIDENT FACTURE ;

           

          // ELimination des doublons propres à AVOIR

          Concatenate( tmp_Link_Table )

          LOAD DISTINCT

               [N° client] ,

               date ,

               ref

          RESIDENT AVOIR ;

           

           

          // Elimination des doublons dus aux combinaisons

          // présentes dans les deux tables de fait

          Link_Table :

          NoConcatenate

          LOAD DISTINCT

               *

          RESIDENT tmp_Link_Table ;

           

          DROP TABLE tmp_Link_Table ;

           

           

           

           

          FOR EACH table IN 'FACTURE' , 'AVOIR' , 'Link_Table'

           

           

           

               LEFT JOIN( $(table) )

               LOAD

           

                         // champs_de_jointure

                        [N° client] ,

                         date ,

                         ref ,

           

                         //clé additionnelle

                         [N° client] & '|&|' & date & '|&|' & ref as clé

               RESIDENT $(table) ;

           

           

               // On ne garde les champs problématiques que dans la Link_Table

               IF '$(table)' <> 'Link_Table' THEN

           

                    DROP FIELDS [N° client] , date , ref FROM $(table) ;

           

               ENDIF

           

           

           

          NEXT table ;

           

           

           

           

           

           

           

           

           

           

          Ce message a été modifié par: Richard Pressanti: Correction d'erreurs de frappe.

           

           

          Ce message a été modifié par: Richard Pressanti: Ajout d'une parenthèse manquante

           

           

          Ce message a été modifié par: Richard Pressanti: Ajout d'une virgule manquante

            • Re: Modélisation en étoiles avec 2 tables de fait

              Bonjour,

               

              Merci de votre retour et pour le code.

              Ok pour passer par une table de lien. Passer par une concatenation de mes 2 tables de fait est également une solution?

               

              Par rapport au code :

               

              J'ai un message d'erreur au chargement des données :

               

               

              Champ introuvable - <N°>
              tmp_Link_Table:

              NoConcatenate
              LOAD DISTINCT
                N°,
                   [N° client],
                   REF,
                   QTE,
                   DATE
              RESIDENT FACTURE

               

              Ci dessous le code que j'utilise :

               

              //---------------------------------------------------------------------------------------------//LET vDateMin = Num(MakeDate(2010,1,1));LET vDateMax = Num(MakeDate(2015,1,1));//Floor(YearEnd(AddMonths(Today(), 12)));LET vDateToday = Num(Today());

              TempCalendar:LOAD$(vDateMin) + RowNo() - 1 AS DateNumber,Date($(vDateMin) + RowNo() - 1) AS TempDateAUTOGENERATE 1WHILE $(vDateMin)+IterNo()-1<= $(vDateMax);

              MasterCalendar:LOADTempDate as KEY_date,Day(TempDate) AS Day,Month(TempDate) AS Month,Year(TempDate) AS Year,Today() as DayNowRESIDENT TempCalendar ORDER BY TempDate ASC;
              DROP TABLE TempCalendar;
              LET vDateMin = Num(MakeDate(2010,1,1));LET vDateMax = Num(MakeDate(2015,1,1));//Floor(YearEnd(AddMonths(Today(), 12)));LET vDateToday = Num(Today());
              //---------------------------------------------------------------------------------------------////STRUCTURATION//QUALIFY*;                                                                                           // Tous les champs sont préfixés par le nom de la table //UNQUALIFY "KEY_*";                                                                           // Tous les champs qui commencent par KEY_xxxx ne seront pas préfixés par le nom de la table ////---------------------------------------------------------------------------------------------//
              CLIENT :LOAD [N° client] AS KEY_client,
                  
              ZoneFROM
              C:\Users\xxxxxxxxx\Desktop\BdD.xls
              (
              biff, embedded labels, table is client$);

              PRODUIT :LOAD REF as KEY_REF,
                  
              GROUPEFROM
              C:\Users\xxxxxxxxx\Desktop\BdD.xls
              (
              biff, embedded labels, table is produit$);

              AVOIR :LOAD ,
                  
              [N° client],
                  
              REF,
                  
              QTE,
                  
              DATEFROM
              C:\Users\xxxxxxxxx\Desktop\BdD.xls
              (
              biff, embedded labels, table is Avoir$);

              FACTURE :LOAD ,
                  
              [N° client],
                  
              REF,
                  
              QTE,
                  
              DATE FROM
              C:\Users\xxxxxxxxx\Desktop\BdD.xls
              (
              biff, embedded labels, table is Facture$);
              //---------------------------------------------------------------------------------------------//
              tmp_Link_Table:
              NoConcatenateLOAD DISTINCT
                     
              ,
                  
              [N° client],
                  
              REF,
                  
              QTE,
                  
              DATE RESIDENT FACTURE ;

              // ELimination des doublons propres à AVOIRConcatenate( tmp_Link_Table )LOAD DISTINCT
                      
              ,
                  
              [N° client],
                  
              REF,
                  
              QTE,
                  
              DATERESIDENT AVOIR ;
              // Elimination des doublons dus aux combinaisons
              // présentes dans les deux tables de faitLink_Table :NoConcatenateLOAD DISTINCT
                   *RESIDENT tmp_Link_Table ;
              DROP TABLE tmp_Link_Table ;

                FOR EACH table IN 'FACTURE' , 'AVOIR' , 'Link_Table'

               
                  
              LEFT JOIN( $(table)
                  
              LOAD

                            
              // champs_de_jointure               [N° client] ,
                            
              DATE ,
                            
              REF

                            
              //clé additionnelle               [N° client] & '|&|' & DATE & '|&|' & REF as clé
                   RESIDENT
              $(table) ;


                  
              // On ne garde les champs problématiques que dans la Link_Table     IF '$(table)' <> 'Link_Table' THEN

                       
              DROP FIELDS [N° client] , DATE , REF FROM $(table) ;

                  
              ENDIF
                 NEXT table ;
              //---------------------------------------------------------------------------------------------//

               

               

              MERCI D'AVANCE POUR VOTRE AIDRE PRECIEUSE

               

              Mathieu

                • Re: Modélisation en étoiles avec 2 tables de fait
                  Richard Pressanti

                  Il vous faut supprimmer la ligne "N° ," qui pose problème. En effet, ce champ n'existe pas et il vous faut utiliser

                  [N° client] comme indiqué dans le code fourni.

                   

                    • Re: Modélisation en étoiles avec 2 tables de fait

                      Aie, j'ai toujours un message d'erreur au chargement

                       

                      Champ introuvable - <N° client>
                      tmp_Link_Table:

                      NoConcatenate
                      LOAD DISTINCT
                           [N° client],
                           REF,
                           DATE 

                      RESIDENT FACTURE

                       

                       

                        • Re: Modélisation en étoiles avec 2 tables de fait
                          Richard Pressanti

                          Il vous faut enlever le QUALIFY * et le UNQUALIFY "KEY_*" puisque avec la méthode Link_Table, vous n'aurez plus de boucles.

                          De même, n renommer pas les champs :

                          REF en KEY_REF

                          et

                          [N° client] en KEY_client

                           

                          Cela donne le code suivant:

                           

                          // Idem Original


                          //---------------------------------------------------------------------------------------------//LET vDateMin = Num(MakeDate(2010,1,1));LET vDateMax = Num(MakeDate(2015,1,1));//Floor(YearEnd(AddMonths(Today(), 12)));LET vDateToday = Num(Today());

                          TempCalendar:LOAD$(vDateMin) + RowNo() - 1 AS DateNumber,Date($(vDateMin) + RowNo() - 1) AS TempDateAUTOGENERATE 1WHILE $(vDateMin)+IterNo()-1<= $(vDateMax);

                          MasterCalendar:LOADTempDate as KEY_date,Day(TempDate) AS Day,Month(TempDate) AS Month,Year(TempDate) AS Year,Today() as DayNowRESIDENT TempCalendar ORDER BY TempDate ASC;
                          DROP TABLE TempCalendar;
                          LET vDateMin = Num(MakeDate(2010,1,1));LET vDateMax = Num(MakeDate(2015,1,1));//Floor(YearEnd(AddMonths(Today(), 12)));LET vDateToday = Num(Today());

                           

                           

                           

                          // Suppression des QUALIFY et UNQUALIFY

                           

                           

                          // Chargement initial sans renommage

                           

                           

                          CLIENT :LOAD [N° client] , // AS KEY_client,
                              
                          ZoneFROM
                          C:\Users\xxxxxxxxx\Desktop\BdD.xls
                          (
                          biff, embedded labels, table is client$);

                          PRODUIT :LOAD REF , // , as KEY_REF,
                              
                          GROUPEFROM
                          C:\Users\xxxxxxxxx\Desktop\BdD.xls
                          (
                          biff, embedded labels, table is produit$);

                          AVOIR :LOAD

                               //,
                              
                          [N° client],
                              
                          REF,
                              
                          QTE,
                              
                          DATEFROM
                          C:\Users\xxxxxxxxx\Desktop\BdD.xls
                          (
                          biff, embedded labels, table is Avoir$);

                          FACTURE :LOAD

                               // ,
                              
                          [N° client],
                              
                          REF,
                              
                          QTE,
                              
                          DATE FROM
                          C:\Users\xxxxxxxxx\Desktop\BdD.xls
                          (
                          biff, embedded labels, table is Facture$);

                           

                           

                          / /Création de la Link_Table => Code du premier post

                           

                          // Elimination des doublons propres à FACTURE

                          tmp_Link_Table:

                          NoConcatenate

                          LOAD DISTINCT

                               [N° client] ,

                               date ,

                               ref

                          RESIDENT FACTURE ;

                           

                          // ELimination des doublons propres à AVOIR

                          Concatenate( tmp_Link_Table )

                          LOAD DISTINCT

                               [N° client] ,

                               date ,

                               ref

                          RESIDENT AVOIR ;

                           

                           

                          // Elimination des doublons dus aux combinaisons

                          // présentes dans les deux tables de fait

                          Link_Table :

                          NoConcatenate

                          LOAD DISTINCT

                               *

                          RESIDENT tmp_Link_Table ;

                           

                          DROP TABLE tmp_Link_Table ;

                           

                           

                           

                           

                          FOR EACH table IN 'FACTURE' , 'AVOIR' , 'Link_Table'

                           

                           

                           

                               LEFT JOIN( $(table)

                               LOAD

                           

                                         // champs_de_jointure

                                        [N° client] ,

                                         date ,

                                         ref

                           

                                         //clé additionnelle

                                         [N° client] & '|&|' & date & '|&|' & ref as clé

                               RESIDENT $(table) ;

                           

                           

                               // On ne garde les champs problématiques que dans la Link_Table

                               IF '$(table)' <> 'Link_Table' THEN

                           

                                    DROP FIELDS [N° client] , date , ref FROM $(table) ;

                           

                               ENDIF

                           

                           

                           

                          NEXT table ;


                           

                           

                           

                          Ce message a été modifié par: Richard Pressanti: Oubli d'une virgule

                            • Re: Modélisation en étoiles avec 2 tables de fait

                              On avance :

                               

                              Les 3 dimensions sont bien liées avec la link_table. Dans la link_table j'ai bien les 3 champs problématiques

                              Le problème maintenant : la link_table n'a pas de jointure avec les 2 tables de fait

                               

                              Lors du chargement des données, j'ai les 3 messages d'erreurs suivant :

                               

                              Erreur de ligne de script :

                              LEFT JOIN( FACTURE

                                   LOAD

                                           

                                            [N° client] ,

                                             DATE ,

                                             REF

                                             

                                             [N° client] & '|&|' & DATE & '|&|' & REF as clé

                                   RESIDENT FACTURE

                               

                              ************************************************************************************

                               

                              Erreur de ligne de script :

                              LEFT JOIN( AVOIR

                                   LOAD

                                           

                                            [N° client] ,

                                             DATE ,

                                             REF

                                             

                                             [N° client] & '|&|' & DATE & '|&|' & REF as clé

                                   RESIDENT AVOIR

                               

                              ************************************************************************************

                               

                              Erreur de ligne de script :

                              LEFT JOIN( Link_Table

                                   LOAD

                                           

                                            [N° client] ,

                                             DATE ,

                                             REF

                                             

                                             [N° client] & '|&|' & DATE & '|&|' & REF as clé

                                   RESIDENT Link_Table

                               

                              ************************************************************************************

                               

                              Au niveau du script, il semble que Qlikview n'aime pas tellement le code suivant :

                               

                               

                              //clé additionnelle
                              [N° client] & '|&|' & DATE & '|&|' & REF as clé
                              RESIDENT
                              $(table)

                               

                               

                               

                              Merci pour votre aide

                               

                              Mathieu

                    • Re: Modélisation en étoiles avec 2 tables de fait

                      En faisant :

                       

                      sum

                       

                      ( aggr(Only( Montant_Facture),clé,[N° document]

                      ))

                       

                      ça marche ! Ouf

                       

                      Merci

                        • Re: Modélisation en étoiles avec 2 tables de fait

                          Aie, non ça ne marche pas

                            • Re: Modélisation en étoiles avec 2 tables de fait
                              Richard Pressanti

                              Quelles sont les dimensions de votre tableau ?

                              En fait, j'utlisais Only() pour éviter les double compte, en pensant que seule la clé déterminait un Montant_Facture: peut être que le problème vient de là ?

                              Pourriez-vous poster uen application, quitte à charger de fausses données ou à les brouiller ?

                               

                                • Re: Modélisation en étoiles avec 2 tables de fait
                                  Richard Pressanti

                                  Au fait, d'où sort [N° Document] ? il n'apparait pas dans la structure des tables...

                                   

                                  • Re: Modélisation en étoiles avec 2 tables de fait

                                    Les dimensions de mon tableau :

                                     

                                    ANNEE / MOIS / N° DOCUMENT

                                     

                                    Comme expressions j'utilise :

                                     

                                    sum

                                     

                                    ( aggr(DISTINCT( Montant_Facture),[N° document],clé

                                    ))

                                     

                                     

                                    J'ai rajouté le N° document (qui est le N° de la facture) afin de localiser le problème. Normalement je ne l'utilise pas.

                                     

                                    La clé : CLIENT DATE REF ne permet pas d'identifier le montant_Facture. En effet, 1 même client peut passer plusieurs commandes à la même date et sur des mêmes ref

                                     

                                    Autre question par rapport à la clé : si un client n'effectue pas d'avoir ne risque t'il pas d'être exclu même s'il à une facture?

                                     

                                     

                                    Désolé je ne peux pas vous donner plus d'infos, je pars en vacances dans 10 min.

                                    Je vous fournirai plus de données à mon retour

                                     

                                    Merci d'avance pour votre aide

                                     

                                     

                                    Ci dessous le code que j'utilise :

                                     

                                    //---------------------------------------------------------------------------------------------//DebutH = now(1);                                                         // Récupère l'heure où le script s'exécute ////---------------------------------------------------------------------------------------------//
                                    //CREATION DU CALENDRIER (Considéré comme une dimension TEMPS)---------------------------------//LET vDateMin = Num(MakeDate(2010,1,1));LET vDateMax = Num(MakeDate(2015,1,1)); //code init : Floor(YearEnd(AddMonths(Today(), 12)));-//LET vDateToday = Num(Today());

                                    TempCalendar:LOAD$(vDateMin) + RowNo() - 1 AS DateNumber,Date($(vDateMin) + RowNo() - 1) AS TempDateAUTOGENERATE 1WHILE $(vDateMin)+IterNo()-1<= $(vDateMax);

                                    MasterCalendar:LOADTempDate AS DATE,Day(TempDate) AS Day,Month(TempDate) AS Month,Year(TempDate) AS Year,Today() as DayNowRESIDENT TempCalendar ORDER BY TempDate ASC;
                                    DROP TABLE TempCalendar;
                                    LET vDateMin = Num(MakeDate(2010,1,1));LET vDateMax = Num(MakeDate(2015,1,1));  //code init : Floor(YearEnd(AddMonths(Today(), 12)));-//LET vDateToday = Num(Today());
                                    //---------------------------------------------------------------------------------------------//
                                    //CHARGEMENT INITIAL (données NAV) SANS RENOMMAGE ---------------------------------------------//OLEDB CONNECT TO xxxxxxxxxxxxxxxxx

                                     

                                    //Dimension CLIENT --------------------------------------------// SQL SELECT AdrPTT1,
                                        AdrPTT2,
                                        AdrPTT3,
                                        "Bloqué" as Blocked,
                                        "Code postal",
                                        "Code vendeur",
                                        Nom,
                                        "N°" as CLIENTS,
                                        "Prénom",
                                        "Secteur client",
                                        Ville
                                    FROM "xxxxxxxxxx".dbo."a_Client";

                                    ARTICLE :                                     
                                    //Dimension ARTICLE --------------------------------------------//SQL SELECT "Bloqué",
                                        "Code axe principal 1",
                                        "Code axe principal 2",
                                        "Code catégorie article",
                                        "Code groupe produits",
                                        "CODE INVENTAIRE",
                                        "Désignation",
                                        "N° article" as REF
                                    FROM "xxxxxxxxx".dbo."a_Article";

                                    FACTURE:                                      
                                    //Table de fait Facture------------------------------------------//SQL SELECT "Date comptabilisation" as DATE,
                                        "Montant" as Montant_Facture,
                                        "N°" as REF,
                                        "N° client facturé" as CLIENTS,
                                        "N° document",
                                        "Quantité" as "Qte_Facture"
                                    FROM "xxxxxxxxx".dbo."a_ET Facture + Ligne  Vente";

                                    AVOIR :                                             
                                    //Table de fait Avoir--------------------------------------------//SQL SELECT "Date comptabilisation" as DATE,
                                        Montant as Montant_Avoir,
                                        "N°",
                                        "N° client facturé" as CLIENTS,
                                        "Quantité" as Qte_Avoir,
                                        REF as REF
                                    FROM "xxxxxxxx".dbo."a_ET Facture + Ligne Avoir";
                                    //-----------------------------------------------------------------------------------------------//
                                    //CREATION DE LA LINK TABLE ---------------------------------------------------------------------//
                                    // Elimination des doublons propres à FACTURE
                                    tmp_Link_Table:NoConcatenateLOAD DISTINCT
                                        
                                    DATE,
                                        
                                    CLIENTS,
                                        
                                    REFRESIDENT FACTURE ;
                                    // Elimination des doublons propres à AVOIRConcatenate( tmp_Link_Table )LOAD DISTINCT
                                        
                                    CLIENTS,
                                        
                                    DATE,
                                        
                                    REFRESIDENT AVOIR ;
                                    // Elimination des doublons dus aux combinaisons présentes dans les deux tables de fait
                                    Link_Table :NoConcatenateLOAD DISTINCT
                                         *RESIDENT tmp_Link_Table ;
                                    DROP TABLE tmp_Link_Table ;
                                    FOR EACH table IN 'FACTURE' , 'AVOIR' , 'Link_Table'
                                     
                                        
                                    LEFT JOIN ($(table))
                                        
                                    LOAD
                                                 
                                    // champs_de_jointure               CLIENTS ,
                                                 
                                    DATE ,
                                                 
                                    REF,
                                                 
                                    //clé additionnelle              CLIENTS & '|&|' & DATE & '|&|' & REF as clé
                                        
                                    RESIDENT $(table) ;

                                        
                                    // On ne garde les champs problématiques que dans la Link_Table     IF '$(table)' <> 'Link_Table' THEN

                                             
                                    DROP FIELDS CLIENTS , DATE , REF FROM $(table) ;

                                        
                                    ENDIF
                                      NEXT table ;
                                    //---------------------------------------------------------------------------------------------//FinH = now(1);                                                                  // Récupère l'heure où le script s'arrête ////---------------------------------------------------------------------------------------------//


                                    CLIENT :