Input:
Load *, Item & '|' & MonthYear as Key;
LOAD [Item Code] as Item,
Rate,
[Wef Date] as WDate,
Date(MonthStart([Wef Date]),'MMM-YYYY') as MonthYear,
1 as Flag
FROM
Test.xlsx
(ooxml, embedded labels, table is Sheet1);
TempTable:
Load Distinct Item Resident Input;
Join
Load Distinct MonthYear Resident Input;
Concatenate(Input)
Load * Where Not Exists(Key);
Load Item, MonthYear, Item & '|' & MonthYear as Key Resident TempTable;
Left Join (Input)
Load Item, Date(Min(MonthYear),'MMM-YYYY') as MinMonthYear Resident Input
Where Flag = 1
Group By Item;
Final:
Load
Item,
MonthYear,
If(Item = Previous(Item) and MonthYear < MinMonthYear, 0,
If(Item <> Previous(Item) and MonthYear < MinMonthYear, 0,
If(Item = Previous(Item) and MonthYear = MinMonthYear, Rate,
If(Item = Previous(Item) and MonthYear > MinMonthYear and Not IsNull(Rate), Peek('Rate'),
Peek('Rate'))))) as Rate
Resident Input
Order By Item, MonthYear;
Drop Table Input;
