Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

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

Re: Count and Group by Clause

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;

4 Replies

Re: Count and Group by Clause

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

hope this helps

regards

Marco

el_aprendiz111
Valued Contributor

Re: Count and Group by Clause

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

Re: Count and Group by Clause

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

Re: Count and Group by Clause

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

Community Browser