Records status over time problem

[#1]

Hello,

Ive been working on a task for a while and havent found a working solution for my problem.

The problem in a nutshell;

Client          Status          Date

1001               1                 20-01-2012           

1001               3                 26-01-2012

1001               2                 15-03-2012

1001               4                 19-03-2012

1002               2                 20-02-2012

1002               4                 21-02-2012

1002               1                 24-02-2012

1002               3                 10-03-2012

1002               1                 15-03-2012

Status 1 and 2 are active flags.

Status 3 and 4 are non-active flags.

What im looking for i need to know, which Clients have been active during certain month meaning that last flag is either 1 or 2. For example in the example the Client=1001 have been active in January, February but not in March and active again in April.

Other variable I need to take into account is that Clients also have permanent Closure date.

Hopefully someones been working with similar problems and have useful solution to offer.

/Matt

[Gysbert_Wassenaar · Accepted solution]

By filling in the gaps between the dates you get the status per client per date. That makes it easy to select the active status values and select a month and see which clients were active in that month. See attached qvw.

[#3]

Thank you for the quick response!

This approach seems really good. It really works flags the status for each day, just the way I was looking for.

One thing that was actually missing from the question was that, I havent found a way to generate dates until today.

Meaning that, like in the example that if Client=1002 last Status=1 is generated for each day until today. So it would be possible to see that Client=1002 has been on Status=1 since 15-03-2012 until today.

Your help is really appreciated!

/Matt

[#4]

Might be that i found solution for this.

I also have ending date for each customer and could use that.

Code;

T1:

LOAD Client,

     Status,

    Date,

     if(isnull(Enddate),Today(),Enddate) as Enddate

FROM

C:\Testi.xlsx

(ooxml, embedded labels, table is Form1);

T2:

Load *, if(previous(Client)=Client, previous(Date),Enddate) as NextDate,

Resident T1

order by Client, Date desc;

drop table T1;

T3:

NoConcatenate

LOAD Client, Status,

date(Date + IterNo() -1) as Date,

date(Monthstart(Date + IterNo() -1),'YYYYMM') as Month,

Enddate

Resident T2

while Date + IterNo() -1 < NextDate;

drop table T2;

This generates dates until today or until the ending date.

/Matt