Counting statuses within an ID to be able to count the IDs

[#1]

Hi

I need some advice. I am trying to count the ID numbers where a certain status appears more than once on different days. So if you look at the basic example below if I want to count the number of times the In Progress status appeared within a single IDNumber I should be getting an answer of 3 (because the one In Progress appears twice on the same date) and then I want to add up all the IDNumbers where the In Progress Status is more than 1.

IDNumber	Status	StatusDate
12345	Open	2015/01/03
12345	In Progress	2015/01/04
12345	Being Reviewed	2015/01/05
12345	In Progress	2015/01/06
12345	In Progress	2015/01/06
12345	Being Reviewed	2015/01/07
12345	In Progress	2015/01/08
12345	Being Reviewed	2015/01/09
12345	Closed	2015/01/10

I have tried a few different methods but none of them are giving me the result that I want. Any suggestions?

Thanks

Colleen

[tresesco]

Do you want the output in text box? And logic like: Number of such IDNumber where Status 'In Progress' appears more than once in a day +  How many times the(more than one) Status 'In Progress' appears in a day? For multiple IDs, how would you expect the output?

[#3]

Ideally I would like to actually put it in the script so that it flags any ID numbers that have the In Progress status appearing more than once and then just be able to use in the set analysis the count function to count the ID numbers where the flag is Y for example.

At this point I am just trying to get what I want on the front end so that I can try and use the same kind of logic in the script.

[tresesco]

Try like:

Load * Inline [
IDNumber,Status,StatusDate
12345,Open,42007
12345,In Progress,42008
12345,Being Reviewed,42009
12345,In Progress,42010
12345,In Progress,42010
12345,Being Reviewed,42011
12345,In Progress,42012
12345,Being Reviewed,42013
12345,Closed,42014
];

T2:
Load
*,
If(Count(if(Status='In Progress', IDNumber))>1, 'Duplicate','OK') as StatusFlag
Resident T1
Group By IDNumber, Status, StatusDate;

DROP Table T1;

[#5]

Thanks but Qlikview doesn't like the calculation and grouping that you put in there.

I have managed to get it to do what I want on the front end by using the following calculated dimension:

=if(aggr(Count(distinct StatusDate),ID,Status)>1,'Y','N')

I just need to make it work in the script

[Kushal_Chawda]

try this

Table:

LOAD * Inline [

IDNumber, Status, StatusDate

12345, Open, 2015/01/03

12345, In Progress, 2015/01/04

12345, Being Reviewed, 2015/01/05

12345, In Progress, 2015/01/06

12345, In Progress, 2015/01/06

12345, Being Reviewed, 2015/01/07

12345, In Progress, 2015/01/08

12345, Being Reviewed, 2015/01/09

12345, Closed, 2015/01/10 ];

New:

LOAD Distinct *,

AutoNumber(StatusDate,Status) as Cnt

Resident Table

;

Left Join

LOAD Status,

max(Cnt) as Count

Resident New

Group by Status;

DROP Table Table;

DROP Field Cnt;

[jonathandienst]

Perhaps you did not notice tresesco's typo: You need a T1 label for the inline load.

T1:

Load * Inline [

IDNumber,Status,StatusDate

12345,Open,42007

....

[#8]

That wasn't an issue because obviously my script is not quite this simple so I was just using his suggestion as a base

[#9]

Thanks I got it to reload now (I have a few more fields in my table and had to put them all into the Group By statement to get it to work). Just have to check that it is doing it correctly - thanks