Invalid Expression

[Alabaster1]

I'm getting an invalid expression error in this section, but I'm very new to this and don't know what I'm doing wrong. Please advise!

Concatenate

LOAD DISTINCT
[!1],
SPCode,
PROD_CD_GRP AS RATE_SERIES_AC,
PLAN_CODE,
'XXXXXXXXXX' AS SORCE_POLCY_ID,
AS_OF_DATE,
WELLNESS_BENEFIT,
PLAN_TYPE,
PLAN_LEVEL,
COMPANY,
MARKET_SEGMENT,
SEX,
AVG(UNITS) AS UNITS,
RIDER AS COVG_IND,
'' as ISSUE_ST,
AGE_AT_ENTRY,
'$(vYear)' as ENTRY_YEAR,
1 as ENTRY_MONTH,
1 as ENTRY_DAY,
((avg(ANNUAL_PREM))/(avg(UNITS))) AS ANN_PREM_PU,
'XXXXXXXX' AS ORG_BCN,
'XXXXXXXX' AS CURR_BCN,
12 AS PREM_FREQ,
SUM(INIT_COV_COUNT) AS INIT_POLS_IF,
IF(FAMILY_CD='0','1',FAMILY_CD) AS FAMILY_TYPE,
5 AS GTEE_PER_Y,
ACC_EP,
SICK_EP,
100 AS COVERAGE_TERM_AGE,
(sum(WOP_BENFT_AMNT))/(sum(UNITS)) AS WOP_BENFT_AMNT_PU,
MAX(GAAP_LKUP_ALR_MORBD),
MAX(GAAP_LKUP_ALR_LAPSE),
MAX(GAAP_LKUP_ALR_MRTLY),
MAX(GAAP_LKUP_ALR_INT_RATE),
MAX(STAT_LKUP_ALR_MORBD),
MAX(STAT_LKUP_ALR_LAPSE),
IF(DEFAULT = 'Y', '190001', MAX(STAT_LKUP_ALR_MRTLY)) AS STAT_LKUP_ALR_MRTLY,
'' AS COHORT_CD,
'' AS INIT_DAC_EXPNS_VOL,
'' AS INIT_DAC_CMMSN_VOL
RESIDENT ACCIDENT_STAT_MORT1
WHERE (ISSDATE >= STAT_LKUP_ALR_MRTLY AND ISSDATE <= STAT_MAX_ALR_MRTLY) OR DEFAULT = 'Y'
ORDER BY SPCode;

[Saravanan_Desingh · Accepted solution]

You are using aggregation function.

SUM(INIT_COV_COUNT) AS INIT_POLS_IF,

So, you should have Group By Clause.

[Vegar · Accepted solution]

As @Saravanan_Desingh  is saying. You are using a lot of aggregation function that needs a group by clause for all the dimensions you want to keep. Take a look at this page: Aggregation functions ‒ QlikView

Try something like this.

LOAD DISTINCT
	[!1],
	SPCode,
	PROD_CD_GRP AS RATE_SERIES_AC,
	PLAN_CODE,
	'XXXXXXXXXX' AS SORCE_POLCY_ID,
	AS_OF_DATE,
	WELLNESS_BENEFIT,
	PLAN_TYPE,
	PLAN_LEVEL,
	COMPANY,
	MARKET_SEGMENT,
	SEX,
	AVG(UNITS) AS UNITS,  /*Aggregation function*/
	RIDER AS COVG_IND,
	'' as ISSUE_ST,
	AGE_AT_ENTRY,
	'$(vYear)' as ENTRY_YEAR,
	1 as ENTRY_MONTH,
	1 as ENTRY_DAY,
	((avg(ANNUAL_PREM))/(avg(UNITS))) AS ANN_PREM_PU, /*Aggregation function*/
	'XXXXXXXX' AS ORG_BCN,
	'XXXXXXXX' AS CURR_BCN,
	12 AS PREM_FREQ,
	SUM(INIT_COV_COUNT) AS INIT_POLS_IF, /*Aggregation function*/
	IF(FAMILY_CD='0','1',FAMILY_CD) AS FAMILY_TYPE, 
	5 AS GTEE_PER_Y,
	ACCEP,
	SICK_EP,_
	100 AS COVERAGE_TERM_AGE,
	(sum(WOP_BENFT_AMNT))/(sum(UNITS)) AS WOP_BENFT_AMNT_PU, /*Aggregation function*/
	MAX(GAAP_LKUP_ALR_MORBD), /*Aggregation function*/
	MAX(GAAP_LKUP_ALR_LAPSE), /*Aggregation function*/
	MAX(GAAP_LKUP_ALR_MRTLY), /*Aggregation function*/
	MAX(GAAP_LKUP_ALR_INT_RATE), /*Aggregation function*/
	MAX(STAT_LKUP_ALR_MORBD), /*Aggregation function*/
	MAX(STAT_LKUP_ALR_LAPSE), /*Aggregation function*/
	IF(DEFAULT = 'Y', '190001', MAX(STAT_LKUP_ALR_MRTLY)) AS STAT_LKUP_ALR_MRTLY, /*Aggregation function*/
	'' AS COHORT_CD,
	'' AS INIT_DAC_EXPNS_VOL,
	'' AS INIT_DAC_CMMSN_VOL
RESIDENT ACCIDENT_STAT_MORT1
WHERE (ISSDATE >= STAT_LKUP_ALR_MRTLY AND ISSDATE <= STAT_MAX_ALR_MRTLY) OR DEFAULT = 'Y'
Group by 
	[!1],
	SPCode,
	PROD_CD_GRP,
	AS_OF_DATE,
	WELLNESS_BENEFIT,
	PLAN_TYPE,
	PLAN_LEVEL,
	COMPANY,
	MARKET_SEGMENT,
	SEX,
	RIDER,
	AGE_AT_ENTRY,
	FAMILY_CD,
	ACCEP,
	SICK_EP,
	[DEFAULT]
;

[Alabaster1]

Thank you !