topic Re: Records status over time problem in QlikView

Records status over time problem

Mon, 12 Aug 2013 11:56:56 GMT

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

Re: Records status over time problem

Gysbert_Wassenaar — Mon, 12 Aug 2013 14:32:03 GMT

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.

Re: Records status over time problem

Mon, 12 Aug 2013 15:47:42 GMT

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

Re: Records status over time problem

Mon, 12 Aug 2013 16:32:49 GMT

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