Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
What is FieldValue() ? What is the restriction on this function?
There is no attachment.
Or please just explain the logic of the result.
What is you requirement?? Please let us know, then only someone can reach you with a suitable ideas.
What is logic to get the output?
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;
Plz let me know to delete it.