Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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.
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
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