topic Problem with Pivot Table in Francophones

Problem with Pivot Table

Tue, 12 Jan 2016 08:42:22 GMT

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

flochi75 — Tue, 12 Jan 2016 09:51:08 GMT

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

Tue, 12 Jan 2016 09:58:27 GMT

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

cje — Tue, 12 Jan 2016 10:22:08 GMT

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

Tue, 12 Jan 2016 10:29:46 GMT

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

cje — Tue, 12 Jan 2016 11:25:08 GMT

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>

Re: Problem with Pivot Table

Tue, 12 Jan 2016 14:14:25 GMT

merci beaucoup christophe