Comparing with previous id

[Anonymous]

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

[its_anandrjs]

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

[Anonymous]

Thanks for helping. What if the inactive flags fall on weekend, I want that to show on nearest monday

[its_anandrjs · Accepted solution]

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;