I'd do it a little bit simpler, with one time access to the source data and no applymap:
orderdate as [Max oderdate],
FROM [H:\kristel_to_ask_QVcommunity.xls] (biff, embedded labels);
INNER JOIN (OderData) LOAD
max(orderdate) as [Max oderdate]
GROUP BY Region;
Another possibility would be to do this:
Region&'-'&date(orderdate,'YYYYMMDD') as [RegionIndex],
(biff, embedded labels);
max(orderdate) as [Max order date]
Group by Region;
Region&'-'&date([Max order date],'YYYYMMDD') as RegionIndex,
'1' as Region_LastFlag
DROP Table RegionLast;
left join (OrderData)
Region_LastFlag as Region_IsLast
drop table RegionLast1;
where Region_IsLast = '1';
drop table OrderData;
What do you think?
And now when you have different scenario, how do you evaluate them on efficiency?
I don't see why do you need a long and complex script while you can have it short and simple.
As a rule, it is better to have actual fields rather than calculated dimensions - same result but better performance. Still your solution makes sense if the max date is not the absolute max date per Region but within current date selection. Even in this case, I'd rather move this expression from calculated dimension to expression. And, in expression, no need to aggr() because Region is a dimension already, simply:
Instead of doing it so complex,
just do one thing.Use the aggr() function.
No need to use any mapping load.Use a straight table .add one more calculated dimension.in that use the aggr() function.In the expression just write any 1 and then hide the expression.You will find your desired answer.
the aggr() function will be like =aggr(max(orderdate),Region)
Hope u understood.
the aggr() approach is a fast solution to get results on the fly. However, as data volume increases, having a calculated dimension can become so heavy for your application, impacting in performance times. So if you have access to script is better to make as much pre-calculations as you can in script,