Count if certain conditions are met?

[#1]

Hello Experts,

I am a new Qlikview developer, and hoping to seek some answers from the community, here is my problem:

A simple data set that contains Class,Name, ID, CreateDate and I am hoping to create a count variable to capture the number of duplicate IDs when CreateDate  < CreateDate (CreateDate for all the previous records):

Dataset:

Class,Name, ID, CreateDate

A,Jason, 121, 2013-Jan-1

A,Jason, 151, 2013-Feb-1

A,Dick, 121, 2013-Feb-2

B,Jason, 151, 2013-Mar-1

B,Dick, 121, 2013-May-1

B,Jason, 151, 2013-June-1

Here is what I accomplish:

List box select Class = B

Class, Name, ID, count

B,Jason, 151,1

B,Dick, 121,2

B,Jason,151,2

so what the expression for count variable will be? I am stuck, please help, I am currently accessing data from the SQL server, maybe I should use if or case statements in the script box instead of expression? what would be the best way to approach ?

Thank you very much!

[swuehl]

To decide whether to create a script based or front end solution, you first need to know if your results need to be selection sensitive or not. If script based, you need to decide if you want to create the count per class etc.

I don't get why Dick has a count of 2 in your sample data, while his ID 121 doesn't have any duplicates within this selection (Class = B).

[#3]

I am only counting the duplicated IDs; therefore, before 2013-May-1, there are two duplicated IDs of 121, so it returns 2 (selection B is ignored)

I think this will be selection insensitive one for my case, SQL scripting approach will do the trick.

What will be the simple script to capture that?

[swuehl · Accepted solution]

Mayb along these lines:

TMP:

LOAD Class, Name, ID, Date#(CreateDate,'YYYY-MMM-D') as CreateDate

INLINE [

Class,Name, ID, CreateDate

A,Jason, 121, 2013-Jan-1

A,Jason, 151, 2013-Feb-1

A,Dick, 121, 2013-Feb-2

B,Jason, 151, 2013-Mar-1

B,Dick, 121, 2013-May-1

B,Jason, 151, 2013-Jun-1

];

DataSet:

LOAD *, autonumber(CreateDate, ID)-1 as CountDupl

Resident TMP order by ID, CreateDate;

drop table TMP;