Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikView forum consolidation is complete. Labels are now required. LEARN ABOUT LABELS
cancel
Showing results for 
Search instead for 
Did you mean: 
engishfaque
Specialist III
Specialist III

MonthName between Start Date and End Date

Dear All,

I'm working on listed below scenario, and trying to find Month Name from/between Start_Date and End_Date.

    

Employee_IDEmployee_NameStart_DateEnd_Date
1001AAA01-Jan-14
1002BBB27-Feb-1503-Aug-15
1003CCC22-Jul-1429-Jul-14
1004DDD13-Mar-15
1005EEE31-Jul-1402-Aug-15


Required output:

if i select Employee_Name (BBB) then Month Field must show me name of the months means Feb, Mar, Apr, May, Jun, Jul and Aug,

because Start_Date month is Feb and End_Date month is Aug for Employee_Name (BBB).

Same for other Employee_Name.

Kind regards,

Ishfaque Ahmed

1 Solution

Accepted Solutions
MarcoWedel

Hi,

you could create a master calendar and link it to your table using the intervalmatch prefix.

One script to create the calendar could be:

tabCalendar:

LOAD *,

    Day(Date) as Day,

    WeekDay(Date) as WeekDay,

    Week(Date) as Week,

    WeekName(Date) as WeekName,

    Month(Date) as Month,

    MonthName(Date) as MonthName,

    Dual('Q'&Ceil(Month(Date)/3),Ceil(Month(Date)/3)) as Quarter,

    QuarterName(Date) as QuarterName,

    Year(Date) as Year,

    WeekYear(Date) as WeekYear;  

LOAD Date(MinDate+IterNo()-1) as Date

While MinDate+IterNo()-1 <= MaxDate;

LOAD Min(Start_Date) as MinDate,

    Max(End_Date) as MaxDate

Resident yourtable;

the link table could be created something like this:

tabDateLink:

Intervalmatch (Date)

LOAD Start_Date, End_Date

Resident yourtable;

hope this helps

regards

Marco

View solution in original post

3 Replies
MarcoWedel

Hi,

you could create a master calendar and link it to your table using the intervalmatch prefix.

One script to create the calendar could be:

tabCalendar:

LOAD *,

    Day(Date) as Day,

    WeekDay(Date) as WeekDay,

    Week(Date) as Week,

    WeekName(Date) as WeekName,

    Month(Date) as Month,

    MonthName(Date) as MonthName,

    Dual('Q'&Ceil(Month(Date)/3),Ceil(Month(Date)/3)) as Quarter,

    QuarterName(Date) as QuarterName,

    Year(Date) as Year,

    WeekYear(Date) as WeekYear;  

LOAD Date(MinDate+IterNo()-1) as Date

While MinDate+IterNo()-1 <= MaxDate;

LOAD Min(Start_Date) as MinDate,

    Max(End_Date) as MaxDate

Resident yourtable;

the link table could be created something like this:

tabDateLink:

Intervalmatch (Date)

LOAD Start_Date, End_Date

Resident yourtable;

hope this helps

regards

Marco

View solution in original post

engishfaque
Specialist III
Specialist III

Dear Marco,

If you check that, Employee_ID = 1001 record. We have a Start_Date but not End_Date.

How to consider Today Date for End Date where there is no End Date in against of any record?

Kind regards,

Ishfaque Ahmed

MarcoWedel

Hi,

try with

Alt(End_Date,Today()) as End_Date

or

If(Len(End_Date),End_Date,Today()) as End_Date

or somthing like that.

regards

Marco