Hi @abubakarsiddiq7
I generated a table using your rules, you can find it named FACTTABLE.
// Load Sample Data
SampleData:
LOAD
S.no,
Agent,
"Date of Joining",
Alt("Termination Date",Today()) AS "Termination Date"
FROM [lib://Sample/Sample Data.xlsx]
(ooxml, embedded labels, table is Sheet1);
// Creating a Calendar
CalendarTmp:
LOAD
Date(Date) AS Date;
Load Date(MinDate + IterNo() -1 ) AS Date While (MinDate + IterNo() - 1) <= Num(MaxDate);
Load Min("Date of Joining") AS MinDate,
Max("Termination Date") AS MaxDate
Resident SampleData;
// Create a CrossJoin between Calendar and SampleDate to generate date intervals
FactTableTmp:
NoConcatenate
Load Date
Resident CalendarTmp;
Left Join
Load S.no,
"Date of Joining",
"Termination Date"
Resident SampleData;
Drop Table CalendarTmp;
// Set flag 0-1 to each month considering 0 < Date > 15
FactTable2:
Load Distinct
MonthStart(Date) AS Date,
S.no,
If(MonthName(Date)=MonthName("Date of Joining") OR
(Day("Date of Joining")>15 AND MonthName(Date)=MonthName("Date of Joining")) OR
(Day("Date of Joining")>15 AND MonthName(Date)=MonthName(AddMonths("Date of Joining",1))),0,1) AS Months
Resident FactTableTmp
WHERE Date>="Date of Joining"
AND Date<="Termination Date";
Drop Table FactTableTmp;
// Calculating M0, M1 and more
FactTable:
NoConcatenate
Load Date,
S.no,
if(S.no=Peek(S.no),RangeSum(Peek(Months),Months),Months) AS Months,
'M' & if(S.no=Peek(S.no),RangeSum(Peek(Months),Months),Months) AS QtMonths
Resident FactTable2
ORDER BY S.no, Date;
Drop Table FactTable2;
Drop Fields Months;
[],
Pedro
