Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Count and Group by Clause

I created a resident table called T5_Retention.  I'm trying to count the number of accounts in each unique grouping using the following script.  The script does not work.  How do I correct this script ?

The expected result should look as shown below.  I attached the qvw document and supporting data.

1 Solution

Accepted Solutions
Not applicable
Author

Hi,

you should list the fields instead of using '*'.

'*' contains also [Customer No] field, which you ommited in Group by clause. And this is cousing an error.

LOAD Code,Entity_Status2,Studflag,Profession,Yrsins3,Count ([Customer No]) as Policies

Resident T5_Retention

Group by Code,Entity_Status2,Studflag,Profession,Yrsins3;

View solution in original post

4 Replies
MarcoWedel

a more dynamic solution might be to count in a chart expression instead of the script.

hope this helps

regards

Marco

el_aprendiz111
Specialist
Specialist

Hi David,

T5_Retention:
LOAD *, 1 AS Policies;
LOAD
* Inline
[
Account No,     Code, Entity_Status2, Studflag,  Profession,     Yrsins3,   Base Premium
615560545, APROFCON23,     A,   PROF, CON, 23,  669
617485102, APROFNEW1, A,   PROF, NEW, 1,   500
597388733, APROFNEW23,     A,   PROF, NEW, 23,  833
615091182, APROFNEW23,     A,   PROF, NEW, 23,  846
615238701, APROFNEW23,     A,   PROF, NEW, 23,  1028
246554344, APROFNEW99,     A,   PROF, NEW, 99,  192
298933256, APROFNEW99,     A,   PROF, NEW, 99,  600
415389121, APROFNEW99,     A,   PROF, NEW, 99,  625
256038801, APROFNEW99,     A,   PROF, NEW, 99,  1349
415354630, APROFNEW99,     A,   PROF, NEW, 99,  2701
619225814, TPROFNEW1, T,   PROF, NEW, 1,   846
615570346, TPROFNEW23,     T,   PROF, NEW, 23,  600
274411835, TPROFNEW99,     T,   PROF, NEW, 99,  814
273466366, TPROFPHY99,     T,   PROF, PHY, 99,  1237
273038314, TPROFPHY99,     T,   PROF, PHY, 99,  1988
246961001, TPROFPPM99,     T,   PROF, PPM, 99,  649
255892592, TPROFPPM99,     T,   PROF, PPM, 99,  796
252198117, TPROFPPM99,     T,   PROF, PPM, 99,  873
256061317, TPROFPPM99,     T,   PROF, PPM, 99,  916
265678272, TPROFPPM99,     T,   PROF, PPM, 99,  2404
]
;


VARIOUS.png

Not applicable
Author

Hi,

you should list the fields instead of using '*'.

'*' contains also [Customer No] field, which you ommited in Group by clause. And this is cousing an error.

LOAD Code,Entity_Status2,Studflag,Profession,Yrsins3,Count ([Customer No]) as Policies

Resident T5_Retention

Group by Code,Entity_Status2,Studflag,Profession,Yrsins3;

Not applicable
Author

Your solution was perfect.  Thank you so much.  I greatly appreciate your help and the help from Marco and fer fer.