Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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;
a more dynamic solution might be to count in a chart expression instead of the script.
hope this helps
regards
Marco
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
];
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;
Your solution was perfect. Thank you so much. I greatly appreciate your help and the help from Marco and fer fer.