I want to find whether any data is missing in latest month for the 2 dimensions(Project & Location). I am only interested in missing data which is listed in 'Location_Map' table.
In my Fact table Product C with Location UK has missing data. So i want to show this in a listbox.
My fact table also contain a flag to identify latest records.
Please help how to get the missing data list.
//Script & Data
Location_Map: Mapping LOAD * INLINE [ Product, Location A, US C, UK ];
Data: LOAD *, Date(MonthStart(Date),'MMM-YYYY') as MonthYear, num(Date) as MonthNum INLINE [ Product, Date, Margin, Sales, Location A, 01/01/2017, 1184632, 4227592, US A, 01/02/2017, 1273124, 4545040, US A, 01/03/2017, 112483, 130487, US B, 01/03/2017, 136175, 145455, UK C, 01/02/2017, 11188205, 11361659, UK ];
maxMonth_map: Mapping LOAD max(MonthYear) as MaxMonth, 1 as MaxFlag Resident Data;
Fact: LOAD *, ApplyMap('maxMonth_map',num(MonthYear),0) as MaxFlag Resident Data;