Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

qlik_aparnav
Contributor II

Comparing with previous id

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

Tags (1)
1 Solution

Accepted Solutions

Re: Comparing with previous id

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;

3 Replies

Re: Comparing with previous id

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

Image2.PNG

qlik_aparnav
Contributor II

Re: Comparing with previous id

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

Re: Comparing with previous id

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;

Community Browser