Missing dates

[yoganantha321]

Hi all,

I have a data set like shown in image 1:

Image 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 2

Can any one help in this!!!!!

Regards,

Yoganantha Prakash G P

[sunny_talwar · Accepted solution]

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;