Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
How to get all months that fall between two date/Month.
For eg. in below screen shot EMP Id =1, is associated with two month 1 (Jan) and 6 (June).
Desired Output should be like :
TEST2.EmpId | TEST2.Month1 | TEST2.Monttname_final |
1 | 1 | Jan |
1 | 2 | Feb |
1 | 3 | Mar |
1 | 4 | April |
1 | 5 | May |
1 | 6 | Jun |
2 | 4 | Apr |
3 | 2 | Feb |
3 | 3 | March |
3 | 4 | April |
3 | 5 | May |
Below is the code i have used so far, but stuck with the month range part.Kinldy help
MonthDetails:
LOAD * INLINE [
Month, MonthName
1, Jan
2, Feb
3, Mar
4, Apr
5, May
6, Jun
7, Jul
8, Aug
9, Sep
10, Oct
11, Nov
12, Dec
];
MLoad:
Mapping Load Month,MonthName Resident MonthDetails;
TEST:
LOAD *,SubField(WDate,'/',2) as 'Month1',
ApplyMap('MLoad',SubField(WDate,'/',2)) as Monttname_final
INLINE [
Emp Id, Dept, WDate
1, Admin, 1/1
1, Store, 3/6
2, HR, 2/4
3, Infra, 15/2
3, Store, 5/5
];
Hi Niket,
Try the WHILE cause, something like:
T1: LOAD * INLINE [ Emp Id, Month#, Month 1,1,Jan 1,6,Jun 2,4,Apr 3,2,Feb 3,5,May ]; T2: LOAD [Emp Id], Min(Month#) AS MinMonth#, Max(Month#) AS MaxMonth# RESIDENT T1 GROUP BY [Emp Id] ; DROP TABLE T1; T3: LOAD [Emp Id], MinMonth#+IterNo()-1 AS Month# RESIDENT T2 WHILE MinMonth#+IterNo()-1
Regards,
David
try this
TEST:
LOAD *,SubField(WDate,'/',2) as MonthNum1
INLINE [
Emp Id, Dept, WDate
1, Admin, 1/1
1, Store, 3/6
2, HR, 2/4
3, Infra, 15/2
3, Store, 5/5 ];
Link1:
LOAD [Emp Id],
min(MonthNum1) as MinMonthNum,
max(MonthNum1) as MaxMonthNum
Resident TEST
Group by [Emp Id];
Link:
LOAD *,
MinMonthNum+IterNo()-1 as MonthNum,
month(makedate(Year(Today()), MinMonthNum+IterNo()-1,1)) as MonthName
Resident Link1
While MinMonthNum+IterNo()-1<=MaxMonthNum;
DROP Table Link1;
DROP Fields MinMonthNum,MaxMonthNum,MonthNum1;
Thanks Kushal, this is what I exactly looking for..Cheers!!!
no problem bro.