Qlik Community

Qlik DataMarket Discussions

Discussion Board for collaboration regarding Qlik DataMarket.

rajeshqvd
Contributor

comparing dates

Hi,

i have a Acc_id  that is unique id and date fields, i want to compare current and previous month of Acc_ids if current month id is match to previuos month id then status is active if not match status is inactive...

Thank-You!

1 Solution

Accepted Solutions
MVP
MVP

Re: comparing dates

Check this out:

Table:

LOAD ID,

  Date(Date#(Date, 'MMM-YY'), 'MMM-YY') as Date;

LOAD * Inline [

ID, Date

1, Mar-15

2, Mar-15

3, Mar-15

4, Mar-15

5, Mar-15

6, Mar-15

7, Mar-15

8, Mar-15

9, Mar-15

1, Apr-15

2, Apr-15

3, Apr-15

4, Apr-15

5, Apr-15

6, Apr-15

8, Apr-15

10, Apr-15

12, Apr-15

];

Join(Table)

LOAD Max(Date) as MaxDate

Resident Table;

FinalTable:

LOAD ID,

  Date,

  If(Date = MaxDate, 'Active',

  If(ID = Peek('ID') and Date(MonthStart(Peek('Date'), -1)) = Date(MonthStart(Date)), 'Active', 'Deactive')) as Status

Resident Table

Order By ID, Date desc;

DROP Table Table;


Capture.PNG

7 Replies

Re: comparing dates

There are various ways possible - here you find many useful informations about to create and use period-fields and you could calculate with them: How to use - Master-Calendar and Date-Values

- Marcus

rajeshqvd
Contributor

Re: comparing dates

Thank you  for your fast response,

i already used a master calendar and created a MonthID also but we need a expression to compare Accid is not matching  in both months

MVP
MVP

Re: comparing dates

Are you wanting to do this in the script? Do you have a sample you can share? We might be able to help better if you can share a sample here

Re: comparing dates

Maybe a comparing on record-level within a sorted resident-load with peek/previous might be a solution and here you could find a very good explanation what it is and how to use: Peek() or Previous() ?

- Marcus

rajeshqvd
Contributor

Re: comparing dates

hi Sunny,

in Script, please take a look i attached a image i need a comparison with current and previous month.

dataexcel.PNG

Thank-You!

MVP
MVP

Re: comparing dates

Check this out:

Table:

LOAD ID,

  Date(Date#(Date, 'MMM-YY'), 'MMM-YY') as Date;

LOAD * Inline [

ID, Date

1, Mar-15

2, Mar-15

3, Mar-15

4, Mar-15

5, Mar-15

6, Mar-15

7, Mar-15

8, Mar-15

9, Mar-15

1, Apr-15

2, Apr-15

3, Apr-15

4, Apr-15

5, Apr-15

6, Apr-15

8, Apr-15

10, Apr-15

12, Apr-15

];

Join(Table)

LOAD Max(Date) as MaxDate

Resident Table;

FinalTable:

LOAD ID,

  Date,

  If(Date = MaxDate, 'Active',

  If(ID = Peek('ID') and Date(MonthStart(Peek('Date'), -1)) = Date(MonthStart(Date)), 'Active', 'Deactive')) as Status

Resident Table

Order By ID, Date desc;

DROP Table Table;


Capture.PNG

rajeshqvd
Contributor

Re: comparing dates

Thank-You....Sunny  this is what i want thanks once again.....Smiley Happy

Community Browser