topic Re: Count and Group by Clause in QlikView

Count and Group by Clause

Fri, 18 Nov 2016 22:45:15 GMT

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.

Re: Count and Group by Clause

MarcoWedel — Fri, 18 Nov 2016 23:04:57 GMT

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

hope this helps

regards

Marco

Re: Count and Group by Clause

el_aprendiz111 — Sat, 19 Nov 2016 16:08:41 GMT

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
];

Re: Count and Group by Clause

Sat, 19 Nov 2016 17:15:18 GMT

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;

Re: Count and Group by Clause

Mon, 21 Nov 2016 17:00:24 GMT

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