Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
HI everyone,
I have account ID's with dates. I want to have active and inactive flag based on dates.
Active Flag is min(Date) and inactive flag should be max(Date)+1.
Any suggestions please?
Data is like
ID Date(DD/MM/YY)
1 1/1/2016
1 2/1/2016
2 1/1/2016
1 3/1/2016
2 2/1/2016
2 3/1/2016
3 2/12016
1 4/1/2016
2 4/1/2016
The expected output is the count of active and inactive flags for each date
And then load this way
For Inactive Flags do this way
if(WeekDay(MaxDate)='Sat', Date(MaxDate+2),
if(WeekDay(MaxDate)='Sun', Date(MaxDate+1),MaxDate)) as Date
Source:
LOAD ID,Date(Date#(Date,'DD/MM/YY'),'DD/MM/YY') as Date;
LOAD * INLINE [
ID, Date
1, 1/1/2016
1, 2/1/2016
2, 1/1/2016
1, 3/1/2016
2, 2/1/2016
2, 3/1/2016
3, 2/1/2016
1, 4/1/2016
2, 4/1/2016
];
New:
LOAD
ID,
Date(Min(Date)) as MinDate,
Date(Max(Date)+1) as MaxDate
Resident Source
Group By ID;
DROP Table Source;
Final:
LOAD ID,
MinDate as Date,
WeekDay(MinDate) as WDate,
'Active' as Flag
Resident New;
Concatenate(Final)
LOAD ID,
//MaxDate as Date,
if(WeekDay(MaxDate)='Sat', Date(MaxDate+2),
if(WeekDay(MaxDate)='Sun', Date(MaxDate+1),MaxDate)) as Date,
WeekDay(MaxDate) as WDate,
'InActive' as Flag
Resident New;
DROP Table New;
May be try this
Source:
LOAD ID,Date(Date#(Date,'DD/MM/YY'),'DD/MM/YY') as Date;
LOAD * INLINE [
ID, Date
1, 1/1/2016
1, 2/1/2016
2, 1/1/2016
1, 3/1/2016
2, 2/1/2016
2, 3/1/2016
3, 2/1/2016
1, 4/1/2016
2, 4/1/2016
];
New:
LOAD
ID,
Date(Min(Date)) as MinDate,
Date(Max(Date)+1) as MaxDate
Resident Source
Group By ID;
DROP Table Source;
Final:
LOAD ID, MinDate as Date,
'Active' as Flag
Resident New;
Concatenate(Final)
LOAD ID, MaxDate as Date,
'InActive' as Flag
Resident New;
DROP Table New;
Output is this
Thanks for helping. What if the inactive flags fall on weekend, I want that to show on nearest monday
And then load this way
For Inactive Flags do this way
if(WeekDay(MaxDate)='Sat', Date(MaxDate+2),
if(WeekDay(MaxDate)='Sun', Date(MaxDate+1),MaxDate)) as Date
Source:
LOAD ID,Date(Date#(Date,'DD/MM/YY'),'DD/MM/YY') as Date;
LOAD * INLINE [
ID, Date
1, 1/1/2016
1, 2/1/2016
2, 1/1/2016
1, 3/1/2016
2, 2/1/2016
2, 3/1/2016
3, 2/1/2016
1, 4/1/2016
2, 4/1/2016
];
New:
LOAD
ID,
Date(Min(Date)) as MinDate,
Date(Max(Date)+1) as MaxDate
Resident Source
Group By ID;
DROP Table Source;
Final:
LOAD ID,
MinDate as Date,
WeekDay(MinDate) as WDate,
'Active' as Flag
Resident New;
Concatenate(Final)
LOAD ID,
//MaxDate as Date,
if(WeekDay(MaxDate)='Sat', Date(MaxDate+2),
if(WeekDay(MaxDate)='Sun', Date(MaxDate+1),MaxDate)) as Date,
WeekDay(MaxDate) as WDate,
'InActive' as Flag
Resident New;
DROP Table New;