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

How to get all months that fall between two dates.

Sat, 29 Apr 2017 17:23:28 GMT

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

];

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

daveamz — Mon, 01 May 2017 09:26:47 GMT

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.

Kushal_Chawda — Mon, 01 May 2017 11:29:18 GMT

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;

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

Mon, 01 May 2017 16:17:16 GMT

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

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

Kushal_Chawda — Mon, 01 May 2017 17:11:55 GMT

no problem bro.