Why I am getting t the Invalid expression error message?

[NenadV]

mapPP:
mapping load
'EUS|'&right(FSP.MakingLocation, 4) &'|'& if(upper(rtrim(mid(FSP.ExtPG_Desc, index(FSP.ExtPG_Desc, '|') + 1, 2))) = 'PR', '8000', rtrim(mid(FSP.ExtPG_Desc, index(FSP.ExtPG_Desc, '|') + 1, 4))) &'|'& FSP.Year &'|'& left(FSP.FPeriodYear, 3) as KeyExpATP,
round(sum(FSP.Units)) as ExpATP.SP_PPQty
from ..\ConsolidatedQVD\IBP_ISE_SP_$(vIBP_YearPeriodDP_1).qvd (qvd)
where match(FSP.LineNo, 16, 46, 76, 'B6') and FSP.Year &'-'& left(FSP.FPeriodYear, 3) = '$(vIBP_YearPeriodDP_1)' and match(rtrim(mid(FSP.ExtPG_Desc, index(FSP.ExtPG_Desc, '|') + 1, 4)), '1010', '1011', '1012', '1X57', '1N13', '1U15', '1V16', '1818','1819', '1848', '3K21','3K22', '3K23', '3K24', '3K25', '3K26', '3K27', '3K28', '3K29', '3K30', '3K32', '3K33', '3K34', '3K35', '3K36', '3K37', '3K38', '3K39', '3K41', '3K44', '3K45', '3K46', '3K47', '3K50', '3K60', '2053', '3T56', '1X57', '1Q40', '1Q49', '2M62', '2X61', '2X63', '3N52', '1742', '1I58', '8000', '3E02', '3W03') and rtrim(mid(FSP.ExtPG_Desc, index(FSP.ExtPG_Desc, '|') + 1, 4)) <> '3K43'
group by right(FSP.MakingLocation, 4), rtrim(mid(FSP.ExtPG_Desc, index(FSP.ExtPG_Desc, '|') + 1, 4)), FSP.Year, left(FSP.FPeriodYear, 3);

Thank you

[chriscammers · Accepted solution]

It looks like your expression in your group by does not match what you have in your load list.

mapPP:
mapping
load
   'EUS|' & right(FSP.MakingLocation, 4) 
   & '|' & 
   if(
      upper(rtrim(mid(FSP.ExtPG_Desc, index(FSP.ExtPG_Desc, '|') + 1, 2))) = 'PR',
      '8000', 
      rtrim(mid(FSP.ExtPG_Desc, index(FSP.ExtPG_Desc, '|') + 1, 4))
   ) 
   &'|'& FSP.Year 
   & '|' & left(FSP.FPeriodYear, 3) as KeyExpATP,
   round(sum(FSP.Units)) as ExpATP.SP_PPQty
from ..\ConsolidatedQVD\IBP_ISE_SP_$(vIBP_YearPeriodDP_1).qvd (qvd)
where 
   match(FSP.LineNo, 16, 46, 76, 'B6') 
   and FSP.Year &'-'& left(FSP.FPeriodYear, 3) = '$(vIBP_YearPeriodDP_1)' 
   and match(rtrim(mid(FSP.ExtPG_Desc, index(FSP.ExtPG_Desc, '|') + 1, 4)), '1010', '1011', '1012', '1X57', '1N13', '1U15', '1V16', '1818','1819', '1848', '3K21','3K22', '3K23', '3K24', '3K25', '3K26', '3K27', '3K28', '3K29', '3K30', '3K32', '3K33', '3K34', '3K35', '3K36', '3K37', '3K38', '3K39', '3K41', '3K44', '3K45', '3K46', '3K47', '3K50', '3K60', '2053', '3T56', '1X57', '1Q40', '1Q49', '2M62', '2X61', '2X63', '3N52', '1742', '1I58', '8000', '3E02', '3W03')
   and rtrim(mid(FSP.ExtPG_Desc, index(FSP.ExtPG_Desc, '|') + 1, 4)) <> '3K43'
group by
/* this is what you had 
   right(FSP.MakingLocation, 4),
   rtrim(mid(FSP.ExtPG_Desc, index(FSP.ExtPG_Desc, '|') + 1, 4)),
   FSP.Year,
   left(FSP.FPeriodYear, 3)*/
//Try this
'EUS|' & right(FSP.MakingLocation, 4) 
   & '|' & 
   if(
      upper(rtrim(mid(FSP.ExtPG_Desc, index(FSP.ExtPG_Desc, '|') + 1, 2))) = 'PR',
      '8000', 
      rtrim(mid(FSP.ExtPG_Desc, index(FSP.ExtPG_Desc, '|') + 1, 4))
   ) 
   &'|'& FSP.Year 
   & '|' & left(FSP.FPeriodYear, 3);

PS if you want to add code samples please format neatly so people can read more easily. 

[NenadV]

Thank you