Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Creator II
Creator II

Missing dates

Hi all,

 

I have a data set like shown in image 1:

Image 1Image 1.

But I need to show the data set like missed dates for a particular person (Type: Office [Generated automatically with missed dates] )for the whole year wise as shown in image 2:

 

 Image 2Image 2

Can any one help in this!!!!!

Regards,

Yoganantha Prakash G P

1 Solution

Accepted Solutions
Highlighted

Something like this

Table:
LOAD *,
	 Name & '_' & Date([Start date] + IterNo() - 1) as NameDateKey,
	 Date([Start date] + IterNo() - 1) as Date
While [Start date] + IterNo() - 1 <= [End date];
LOAD * INLINE [
    Name, Start date, End date, Type
    Yoga, 1/2/2018, 20/2/2018, Travel
    Yoga, 5/8/2018, 15/08/2018, Vacation
    Nantha, 1/1/2018, 20/8/2018, Travel
    Nantha, 9/9/2018, 12/12/2018, Vacation
];

Calendar:
LOAD Date(MinDate + IterNo() - 1) as Date
While MinDate + IterNo() - 1 <= MaxDate;
LOAD Min(Date) as MinDate,
	 Max(Date) as MaxDate
Resident Table;

Left Join (Calendar)
LOAD Distinct Name
Resident Table;

Concatenate (Table)
LOAD Name,
	 Date,
	 'Office' as Type
Resident Calendar
Where not Exists (NameDateKey, Name & '_' & Date);

DROP Table Calendar;

TempTable:
LOAD *,
	 If(Name = Previous(Name),
	 	If(Type = Previous(Type), Peek('Value'), RangeSum(Peek('Value'), 1)), 1) as Value
Resident Table
Order By Name, Date;

FinalTable:
LOAD Name,
	 Type,
	 Value,
	 Date(Min(Date)) as [Start date],
	 Date(Max(Date)) as [End date]
Resident TempTable
Group By Name, Type, Value;

DROP Tables Table, TempTable;

View solution in original post

1 Reply
Highlighted

Something like this

Table:
LOAD *,
	 Name & '_' & Date([Start date] + IterNo() - 1) as NameDateKey,
	 Date([Start date] + IterNo() - 1) as Date
While [Start date] + IterNo() - 1 <= [End date];
LOAD * INLINE [
    Name, Start date, End date, Type
    Yoga, 1/2/2018, 20/2/2018, Travel
    Yoga, 5/8/2018, 15/08/2018, Vacation
    Nantha, 1/1/2018, 20/8/2018, Travel
    Nantha, 9/9/2018, 12/12/2018, Vacation
];

Calendar:
LOAD Date(MinDate + IterNo() - 1) as Date
While MinDate + IterNo() - 1 <= MaxDate;
LOAD Min(Date) as MinDate,
	 Max(Date) as MaxDate
Resident Table;

Left Join (Calendar)
LOAD Distinct Name
Resident Table;

Concatenate (Table)
LOAD Name,
	 Date,
	 'Office' as Type
Resident Calendar
Where not Exists (NameDateKey, Name & '_' & Date);

DROP Table Calendar;

TempTable:
LOAD *,
	 If(Name = Previous(Name),
	 	If(Type = Previous(Type), Peek('Value'), RangeSum(Peek('Value'), 1)), 1) as Value
Resident Table
Order By Name, Date;

FinalTable:
LOAD Name,
	 Type,
	 Value,
	 Date(Min(Date)) as [Start date],
	 Date(Max(Date)) as [End date]
Resident TempTable
Group By Name, Type, Value;

DROP Tables Table, TempTable;

View solution in original post