topic Re: Why I am getting t the Invalid expression error message? in QlikView

Why I am getting t the Invalid expression error message?

NenadV — Fri, 03 Jan 2025 12:49:58 GMT

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

Re: Why I am getting t the Invalid expression error message?

chriscammers — Fri, 03 Jan 2025 13:38:42 GMT

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.

Re: Why I am getting t the Invalid expression error message?

NenadV — Fri, 03 Jan 2025 13:59:26 GMT

Thank you