Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I have a data set like shown in 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:
Can any one help in this!!!!!
Regards,
Yoganantha Prakash G P
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;
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;