LOAD count

[#1]

Good morning,

I try to count the nb of reinsurers per file, knowing that my data is grouped by ref1, ref2 and layer_no.

Systems returns "Invalid expression" with the code below. Could you please let me know where is the mistake ?

temp:
LOAD ref1,
ref2,
layer_no,
clm_rein_dist_no,
...,
...

FROM [zzzz] ;

Table_count:
LOAD Distinct
*,
count (clm_rein_dist_no) as NbReassureurs
Resident temp
Group by ref1, ref2, layer_no ;
DROP Table temp;

For example :

ref1	ref2	layer_no	clm_rein_dist_no
PR0015632	A	1	1
PR0015632	A	1	2
PR0015632	A	1	3
PR0015632	A	1	4
PR0015632	A	2	1
PR0015632	A	2	2
PR0015632	B	1	1
PR0015632	B	1	2
PR0015632	B	1	3
PR0015632	B	1	4
PR0015632	B	2	1
PR0015632	B	2	2

I expect the below result :

ref1	ref2	layer_no	NbReassureurs
PR0015632	A	1	4
PR0015632	B	2	2
PR0015632	A	1	4
PR0015632	B	2	2

Thank you in advance for your help

[sunny_talwar · Accepted solution]

Try this

temp:
LOAD ref1,
ref2,
layer_no,
clm_rein_dist_no,
...,
...

FROM [zzzz] ;

Table_count:
LOAD ref1,

    ref2,

    layer_no,
    count (clm_rein_dist_no) as NbReassureurs
Resident temp
Group by ref1, ref2, layer_no ;
DROP Table temp;

[#3]

This works fine, thank you