Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

How to get all months that fall between two dates.

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.EmpIdTEST2.Month1TEST2.Monttname_final
11Jan
12Feb
13Mar
14April
15May
16Jun
24Apr
32Feb
33March
34April
35May

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

];

4 Replies
daveamz01
Contributor III

Re: How to get all months that fall between two dates.

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

Re: How to get all months that fall between two dates.

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;


Not applicable

Re: How to get all months that fall between two dates.

Thanks Kushal, this is what I exactly looking for..Cheers!!!

Re: How to get all months that fall between two dates.

no problem bro.