6 Replies Latest reply: Jan 12, 2016 9:14 AM by Damien Lemoine RSS

    Problem with Pivot Table

      Hello,

      I have a problem that may be related to my model

      Context: Some patients who have concepts (Age, weight, height, disease ...) with attributes (concept of value 35, 50 kg, cancer ...)

       

      I have a patient table

      PatientId, Name

      1 Damien

      2 Julien

      3, Marie

      4, Cedric

       

      and a second table ConceptAttributs

      ConceptId, ConceptCode, AttributValue, #PatientId

      1111 Size, '170', 1

      222, Weight, '65', 1

      333 Age, '50', 1

      1111 Size, '170', 2

      222, Weight, '65', 2

      333 Age, '32', 2

      111, Weight, '50', 3

      1111 Size, '172', 4

      222, Weight, '65', 4

      333 Age, '50', 4

      Damien the patient to a size of 170 and a weight of 65 and an age of 50

      Julien patient to a size of an age of 32 170 and a weight of 65

      Marie patient weighing 50 an age of 32

      Cedric the patient to a size of 172 an age of 50 and a weight of 65

       

      I just want a crosstab according to defined conceptCode

      For example the number of patient's weight and age

      WEIGHT AGE NBPatient

      65 50 2

      65 32 1

      50 32 1

       

      For this I created two dimension

      = if (ConceptCode = 'Weight', AttributValue)

      = if (ConceptCode = 'Age', AttributValue)

       

      And I 'have an expression: count (distinct PatientId)

      When I do that my table displays the first column with the different age

      and the second column with different weight but in a separate lines


       

       

      Thanks for your help

      Damien

        • Re: Problem with Pivot Table
          Florent Lelarge-Otiniano

          Bonjour Damien,

           

          je ne suis pas très doué pour bien comprendre l'anglais, et du coup je ne suis pas sûr de ton pb...

          par contre, si tu as fais un copié collé de ton script, il manque des virgules (en rouge) :

          and a second table ConceptAttributs

          ConceptId, ConceptCode, AttributValue, #PatientId

          1111, Size, '170', 1

          222, Weight, '65', 1

          333, Age, '50', 1

          1111 Size, '170', 2

          222, Weight, '65', 2

          333, Age, '32', 2

          111, Weight, '50', 3

          1111, Size, '172', 4

          222, Weight, '65', 4


          essaie déjà de corriger ça, si le pb persiste, merci de me répondre en français


          Florent.

            • Re: Problem with Pivot Table

              Bonjour Florent

              merci de ta reponse,

              Ce n'est pas un probleme de syntaxe,

              Voila un exemple de script

              Pat:

              LOAD * INLINE [

                  PatId, Name,

                  1, Damien

                  2, Ludo

                  3, Anthony

                  4, Cedric

              ];

               

              ConceptAttribut:

              LOAD * INLINE [

                  ConcId, CodeConcept, Value,PatId

                  1, Age, 35,1

                  2, Poids, 60,1

                  3, Taille, 140,1

                  1, Age, 35,2

                  2, Poids, 60,2

                  3, Taille, 140,2

                  1, Age, 35,3

                  2, Poids, 62,3

                  3, Taille, 145,3

                  1, Age, 34,4

                  2, Poids, 60,4

                  3, Taille, 170,4

              ];

              Et je voudrais faire un pivot table avec le nombre de patients par Age et poids

              je ne fais pas une colonne age poids ... parce que j'ai une infinité de concepts

               

              Mais je n'arrive pas à faire une analye croisée

              lorsque j'essaye j'ai

               

              merci de ton aide

              • Re: Problem with Pivot Table
                Christophe JOUVE

                Hello,

                I don’t understand what you are willing to do with your script, but what I can tell you is :

                 

                -          In your case, it is not necessary to build a calculated dimension. Using associativity in QlikView, you can easily get what you want

                 

                -          Your script has some mistakes which need to be corrected. If you want to use associativity, you must have identical field names in tables that are to be linked

                Here is the correct script :

                patients:

                load * inline

                [PatientId,Name

                1,Damien

                2,Julien

                3,Marie

                4,Cedric

                ];

                 

                Attributs:

                load * inline

                [ConceptId,ConceptCode,AttributValue,PatientId

                1111,Size,170,1

                222,Weight,65,1

                333,Age,50,1

                1111,Size,170,2

                222,Weight,65,2

                333,Age,32,2

                111,Weight,50,3

                1111,Size,172,4

                222,Weight,65,4

                333,Age,50,4

                ];

                 

                Please find attached an example application.

                Regards.

                 

                 

                Christophe Jouve

                Principal Solution Architect

                 

                Direct: +33 1 55 62 65 54

                Mobile: +33 6 76 24 22 47

                Email:  Christophe.Jouve@qlik.com

                 

                Qlik

                France Headquarters Office,93 avenue Charles de Gaulle

                92200 Neuilly sur Seine

                 

                qlik.com<http://www.qlik.com/>

                 

                 

                Téléchargez gratuitement QlikView

                 

                http://www.qlikview.com/fr/explore/experience/free-download

                <http://global.qlik.com/qonnections-2016/?SourceID1=Corporate_Email_Signature>

                 

                The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from any computer.

                  • Re: Problem with Pivot Table

                    Hello christophe

                     

                    Thank you for your answer,

                     

                    script is just here for present you the problem

                    I would like put on the same line two concept for exemple Age et size

                     

                    I want to know number of patient with 32 years ans with a weight of 65

                     

                    thanks again for your time

                      • Re: Problem with Pivot Table
                        Christophe JOUVE

                        Daniel,

                        Pour résoudre simplement ton problème, une des solutions consiste à transposer les données lignes en colonnes. Il s’agit d’une transposition inverse.

                        QlikView ne sait pas le faire nativement, puisque la fonction CROSSTABLE réservée à cela fait l’inverse. Elle transforme des colonnes en lignes.

                        Tu trouveras en pièce jointe une application te permettant de faire ce dont tu as besoin. Regarde le script de chargement. Il y a des commentaires dedans.

                        Cdt

                         

                         

                        Christophe Jouve

                        Principal Solution Architect

                         

                        Direct: +33 1 55 62 65 54

                        Mobile: +33 6 76 24 22 47

                        Email:  Christophe.Jouve@qlik.com

                         

                        Qlik

                        France Headquarters Office,93 avenue Charles de Gaulle

                        92200 Neuilly sur Seine

                         

                        qlik.com<http://www.qlik.com/>

                         

                         

                        Téléchargez gratuitement QlikView

                         

                        http://www.qlikview.com/fr/explore/experience/free-download

                        <http://global.qlik.com/qonnections-2016/?SourceID1=Corporate_Email_Signature>

                         

                        The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from any computer.

                  • Re: Problem with Pivot Table

                    merci beaucoup christophe